Refresh your Power BI Dashboard even if row header has changed / Part II
How to overcome the problem and fix Power BI Dashboard Refresh breaks after row header data has changed
This is a continuation of a prior post. If you want to read the first method, please click on the link below.
Refresh your Power BI Dashboard even if row header has changed / Part I2. Changing row headers dynamically after the import
While the previous method was quick and simple enough, second method addresses this issue dynamically by referencing a specific column row header using some very basic Power Query M Formula Language (if you don’t know about M Language, here you can read more).
After you have imported the data just like in the other method above, go ahead and delete the last applied step Changed Type.
After you delete Changed Type step, with Promoted Headers step selected, go to fx and click on it which will add a new step.
Type the code below and you will get a list with names of row headers for each column.
= Table.ColumnNames (#"Promoted Headers")
If you want to see a specific row header from the list, you need to type the number of the row in curly brackets after the code above. Let’s say we are trying to select Sales June row header, we type {3} after the code above (here is a link if you want to read more on why lists start from 0 and not from 1 for most of the programing languages), and you will get the results below.
= Table.ColumnNames (#"Promoted Headers"){3}
We will use this line of code later when referring to a specific row header which differs from the first method as this one addresses it dynamically by the row number where it is being placed within a list. For now, we add another Custom step by clicking on fx and add the code below which will bring us the entire datset information.
= #"Promoted Headers"
With Custom2 step selected, we go ahead and double click on Sales June row header and replace it with Current Month while for Sales May we replace it with Prior Month. After making those two replacements, your code will look like the below.
= Table.RenameColumns(#"Promoted Headers",{{"Sales June", "Current Month"}, {"Sales May", "Prior Month"}})
This step replaced the row headers, but leaving as is it will not work because as you can see from the code above, it is still addressing the row header column with the previous name which we replaced. Instead, it should refer to it dynamically to the row header as we showed you in previous steps when we filtered for Sales June only.
Go and select Custom1, copy the data from there (do not copy the equal sign), then go to Renamed Columns step, delete ‘‘Sales June’’ and add the code you have copied instead. Also make sure you do the same step for ‘‘Sales May’’ by deleting it and adding the code that refers to it dynamically (this time change the {3} to {4} as Sales May column is next column to Sales June). End code should look like the below.
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames ( #"Promoted Headers"){3}, "Current Month"}, {Table.ColumnNames ( #"Promoted Headers"){4}, "Prior Month"}})
As we can see the only difference from the last two code sections, is the way we refer to a specific header name.
= Table.RenameColumns(#"Promoted Headers",{{"Sales June", "Current Month"}, {"Sales May", "Prior Month"}})vs= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames ( #"Promoted Headers"){3}, "Current Month"}, {Table.ColumnNames ( #"Promoted Headers"){4}, "Prior Month"}})
Next, we go ahead and save changes and it should be all set. Go ahead and try changing the underlying data headers for the last two columns and refresh should work just fine.
This method is much more preferred than the first one because it addresses each row header dynamically and let’s say you have more row headers to change, all you have to do is add those additional steps like we did in the last code section.
Download data sample and Solution here