visuals stop working with dynamic column names

[marcus_giddings]

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.

[marcus_giddings]

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

[Thejeswar]

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

[marcus_giddings]

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

[Thejeswar]

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,

[marcus_giddings]

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

[amitchandak]

@marcus_giddings , check if the attached file can help

Please check power query and the matrix visual