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.

2. 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.

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.

Click on fx which will add a new step

Type the code below and you will get a list with names of row headers for each column.

List of all row 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.

Sales June row header selected based on the code above

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.

Results you get from Custom2 step

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.

Formula bar after changing names for the last two column

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.

Final screen after changes for row headers are made

As we can see the only difference from the last two code sections, is the way we refer to a specific header name.

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.

Go to Close & Apply to save changes

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store