How to Manage Data with Ever Changing Headers in Power Query

twxmargerate
Jul 13, 2024
2 min read

Here's an example of ever changing headers where the dates are the headers of the data, basically means the headers change every month or day.

Transforming such data in Power Query can be challenging as Power Query grabs the headers of the data when transforming the data, so whenever the headers change, Power Query can't recognize the data and gives out an error like the image below.

The issue can be solved by a solution I called it: Grouping the headers/columns.

So here's I do it:

Remove Default Changed Type

After loading the data into Power Query Editor, remove default Changed Type step in the Applied Steps section.

Grouping or Segregate the headers

Define which headers are the ones that are fixed and the ones that are ever changing.

Then open Advanced Editor in Home tab in Power Query Editor.

Then add the below code into the Advanced Editor after the first applied step.

#"Fixed Columns" = {"Column Name"},
#"Changing Columns" = List.Difference(Table.ColumnNames(Previous Step),#"Fixed Columns"),

Replace the highlighted Column Name in #"Fixed Columns" with the constant headers, multiple headers can be added here as well.

Replace the highlighted Previous Step with the previous applied step in this Power Query Editor.

Below is an example for your reference:

After grouping/defining the fixed headers and the ever changing headers, data transformation can be done based on these defined groups.

I have added a section below showing the common data transformation that can be done based on these fixed headers and changing headers.

Data Transformation Examples:

Change Data Type

Here's an example of changing the data type. My fixed column is text type while the changing columns are number type.

#"Changed Type1" = Table.TransformColumnTypes(Source,List.Transform(#"Fixed Columns", each{_,type text})),
#"Changed Type2"= Table.TransformColumnTypes(#"Changed Type1",List.Transform(#"Changing Columns", each{_,type number}))

In #"Changed Type1", I have transformed the #"Fixed Columns" to text type.

In #"Changed Type2", I have transformed the #"Changing Columns" to number type.

Below is the screenshot of the entire process. Remember to replace the step after "in" with the last applied step that have been made.

Replace Value in Changing Headers Columns

#"Replaced Value" = Table.ReplaceValue(Source,"No Sales",0,Replacer.ReplaceValue,#"Changing Columns")

This is an example code of replacing data in the ever changing headers columns. So here I'm replacing the phrase "No Sales" with 0.

Below is the screenshot of the entire process. Again, remember to replace the step after "in" with the last applied step that have been made.

Attached is the sample of data transformation, showcasing examples of changing data type and replacing values for changing headers columns. Please feel free to download it for your reference.

Lastly, hope this simple article can help in your data transformation. Please like this post if you find it helpful~

And have a nice day and happy data analyzing!