Dynamic columns names change ever month

[R_K_Damera]

Hello Everyone, 

This Data set has multiple columns where in Column 7 to Column 12 gets refreshed ever month. 

Example : For this month data starts from Jan 2023 to June 2023 

                For next month data starts from Feb 2023 to july 2023

Here Column 7 starts from jan 2023, Column 8 starts from Feb 2023, Column 9 start from mar 2023,,, Column with  jun 2023

For next month Column 7 start with Feb 2023, Column 8 start with Mar 2023,,, Column 12 with July 2023

The requirement is 

1) All the Column present in the data set should be placed in TABLE Visualization only. 

Problem :

1) when ever the Current month data is added to table Visualization ie., jan 2023 to Jun 2023 the visual looks fine.

2) as data gets refreshed next month (next month data start from. Feb 2023 to July 2023) Table visualization has only Feb, mar, Apr, may, jun. Where in July 2023 is not added to table visual as the Column name got changed hence my visual Breakdown. 

Need Help

•Can we achieve this by index

•Can we achieve this by dax with column7 is replaced by Apr 2023

•can we achieve this by power query dynamically

• is there any way to achieve this. 

[MFelix]

Hi @R_K_Damera ,

For this I believe that the best option is for you to unpivot the data that way you will get a line for each month and you can then use that column in a matrix visualization in order to update the data automatically.

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

[R_K_Damera]

Hi, 

I have tried using unpivot in the matrix visual and when ever user exported the data we are getting attribute, value only 

User wanted the data in the sequence column as the input file

[MFelix]

Is the export from the service or PBI desktop?

In the service you can download the data exactly how you see it in the visual. 

[R_K_Damera]

Using desktop and need help on the same

[MFelix]

Hi @R_K_Damera ,

The main question on this case is the usage of different columns in your visualization since when you name a specific column to Jan 23 that is the name it is going to have in the visualizations also, there is no way for you to have a dynamic naming based on columns names itself.

The dynamic naming can be achieved using the values on a table has I refer doing the unpivot but not from that table exactly.

There are some options to change your model in a dynamic way however that will break your table visualization since has I refered previously the columns name used on the visuals are exactly the ones you have on your model or that you overwrite on the visual.

Only option I can suggest is getting generic names for the columns but I assume you users also want to have the correct dates being used in the visual/download.

Check if this video can help you on dynamic naming.

https://www.youtube.com/watch?v=kwf-E3a3aOE

https://www.youtube.com/watch?v=gndANP-ObQg