Refresh your Power BI even if row header has changed / Part I
How to overcome the problem and fix Power BI Refresh breaks after row header data has changed
Have you ever faced a situation where your underlying data, whether that is an excel file or any other format, if header changes, you will not be able to refresh the data as Power BI will be looking specifically for that column name. I will be providing two methods how to overcome this challenge.
Overall, in cases when you are dealing with data in time series, whether that is specific to a minute/hour/day or any other period, it is better to provide those details within one column (will make your life easier with time intelligence series analysis) and not by adding data to a new column for each month, however that might not be always the case and you might receive data like in our case today.
For those who want to follow along, you can get the data here. It might be tempting to open the Solution file but I would highly suggest to try it yourself and if you get stuck then open the Solution file.As we can see from the sample below, (please note I have provided a small sample for the sake of simplicity), we have Sales data for the months of June and May based on sales representative, country, and region. I was being asked to create a Power BI dashboard in a way that when month changes (i/e from June to July), Power BI should still be able to refresh this data although the row header will change for the columns that include month name.
If you import this data to Power BI, of course you will not be having any issues, but when next month comes, you will not be able to refresh the Power BI dashboard because of the changes that will be made in top row header for the month name and it will show you an error as per below.
In this article, I will show you how to change row header column dynamically so when you refresh it, it does not break like it usually happens.
In order to achieve the results we are looking for, there are two methods (that I am aware of) which I will explain thoroughly. First method, uses somewhat more static way of changing the row headers while the second way refers in more dynamic way with the later being as a better solution for a larger dataset.
Type row headers manually after the import
In cases when you are working with a small dataset and you are focused on achieving results faster, you can use the aforementioned method.
After you import the dataset sample provided, you go to the Power Query Editor which will look like the below.
As you can see, Power BI is smart enough to identify the row header for this data and it takes a few steps automatically in order to promote the first row as a header for the dataset and changes the type according to the data that column contains. In order to achieve the results we are looking for, we can go ahead and delete the last two steps: Promotion Headers and Changed Type by right-clicking and then click Delete.
As we can see below, first row goes below as part of the data and we have Column1, Column2 etc., as row headers because we deleted the steps that promoted the first row as a row header.
We can double click in each row header and manually input the row header for each column.
After we type manually all row headers, we can delete the first row which is redundant because we did a manual input of our row headers and will use them instead ( please note we have used Current Month and Previous Month to represent June and May columns respectively, which is more generic name so whenever there is an update to compare upcoming months, it shows those instead).
After we have made all the changes we need, we can go ahead and delete the first row of data. In order to do so, you can go to Home/Remove Rows/ Remove Top Rows and specify the numbers of rows as 1 which is the row we are trying to delete.
As you can see from the screen below from Applied steps, we have two new steps, one of them where we renamed columns and the other one where we removed the first row.
After all the steps above, we go Home and click Close & Apply button.
After you apply the changes made, try to change the header of the month to July or any other month, you will notice that you will be able to refresh the data without having any issues. The reason why you are not getting an error is because we manually typed the header for each column and whatever is the first header data in the data source it will be deleted and replaced with the one we typed, hence it will work perfectly fine.
Of course, this would work in cases when you have a very basic dataset with a few columns. Imagine if you have a lot of datasets with a lot of columns of data, this process will not be feasible as it will be very time consuming. Second method which will be discussing below will address this issue and ways to handle that.
Changing row headers dynamically after the import
While the other method was quick and simple enough, second method address issues dynamically by referencing a specific column using some very basic Power Query M Formula Language (read more).
As I was about to type the second method Substack is showing me a message ‘Near email length limit’ so I will end it here and continue in another separate email.
In the meantime, here you have the data sample as well as the Power BI file with solution for the first method. Try changing the row headers as well as the amounts for the Current Month as well as Previous month and see if the refresh will pick up the data.
Download data sample and Solution hereOnce I publish the second method, I will post the link in here.
This information is very useful, thank you very much!