Forum Discussion
visuals stop working with dynamic column names
hi all , i have a dashboard that uses weekly sales data from a sql server the table i have looks somthing like this.
| date_lw | sales_lw | date_2w | sales_2w | date_3w | sales_3w | date_4w | sales_4w | date_5w | sales_5w |
| 01/01/2022 | 1000 | 08/01/2022 | 2500 | 15/01/2022 | 1250 | 22/01/2022 | 4000 | 29/01/2022 | 3000 |
my manager whants a table to show this data i a very specific way (replicating a legacy reportt that directors dont want to change), the layout he wants is to have the week number as the colmun header for the weeks sales data something like this.
| 01/01/2022 | 08/01/2022 | 15/01/2022 | 22/01/2022 | 29/01/2022 |
| 1000 | 2500 | 1250 | 4000 | 3000 |
i have been able to get this format in the data by using power query to change the column names, the data refreshes with out issue and the column names do change when the data in the underlying sql server data changes.
The issue that i have is that when the power bi data refreshes any visual that i have that uses one of the columns with a changed names stops working, this leads to an error for example with the column (25/12/2021), this column would no long exsist because the sql server data only contains 5 weeks of rolling data so that every week the dates in each column changes.
The only way around this is that every week i will need to manualy remove the errored column from the visual and add in the column again but no with the updated name and then republish it back. im looking for a way that when the column names update the changes are reflected in the visuals with out manual intervention.
6 Replies
marcus_giddings , check if the attached file can help
Please check power query and the matrix visual
unfortunalty this method does not work for due to the data in my sql server not being structured like this and that the desired table does have more columns that only partain to the sales_lw so using the matrix column method would mean these extra columns being duplicated
Hi marcus_giddings ,
You can fix this by importing the data with column Number as the column names and then promote the Dates as headers.
This way I think you will not have this issue
Regards,
i have looked at you solution but lookingfat you data structure, it does not match the structure in my sql server as this is like this
date_lw sales_lw date_2w sales_2w change in sales numebr of satff that made sales number of people that made purchases 01/01/2022 1000 08/01/2022 2500 1500 10 50 so even if i could use you method the extra columns "change in sales, numebr of satff that made sales and number of people that made purchases" would end up being duplicated for each date which would look like this
01/01/2022 08/01/2022 sales change in sales numebr of satff that made sales number of people that made purchases sales change in sales numebr of satff that made sales number of people that made purchases 1000 1500 10 50 2500 1500 10 50 but what i need is like this
01/01/2022 08/01/2022 change in sales numebr of satff that made sales number of people that made purchases 1000 2500 1500 10 50 somthing i have tried is when changing the column names via power query is instead of referencing the column that i want to change by what is name it content i have tried by referencing the column position in the table by using quueries simalr to this
= Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){3}, dbo_QLIK_WEEKLY_HOURS_no_append[Date_lw]{0}}})
this also doesnt work
Hi,
I am not sure if trying to access the column names like this is possible, instead you can try to import it with column No in to Power BI
