Column Name Changes Every 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. 

[MattAllington · Accepted solution]

The correct approach is to

• promote the first row as column headers
• delete any "changed type" step if it is auto created
• select all columns (I assume all others) except for the columns that are month names and unpivot other columns

this will create a table of data that is independent of any month column names. You can rebuild the columns using a matrix. 

[Anonymous · Accepted solution]

@MattAllington Thanks for the contribution on this thread.

Hi @R_K_Damera ,

I recommend following @MattAllington ’s method, which involves transforming the date column into a column value using the unpivot operation. 

Additionally, consider changing your table visual to a matrix and placing the months in the Columns option of the matrix. This approach ensures that even if the data refreshes monthly, the visual won’t encounter errors related to missing start month columns from previous months.

Best Regards

[MattAllington · Accepted solution]

The correct approach is to

• promote the first row as column headers
• delete any "changed type" step if it is auto created
• select all columns (I assume all others) except for the columns that are month names and unpivot other columns

this will create a table of data that is independent of any month column names. You can rebuild the columns using a matrix. 

[R_K_Damera]

Hi Matt, 

Thanks for reverting.,, 

I have tried however the data is to be shown in Table Visualization instead of Matrix visual

[Anonymous · Accepted solution]

@MattAllington Thanks for the contribution on this thread.

Hi @R_K_Damera ,

I recommend following @MattAllington ’s method, which involves transforming the date column into a column value using the unpivot operation. 

Additionally, consider changing your table visual to a matrix and placing the months in the Columns option of the matrix. This approach ensures that even if the data refreshes monthly, the visual won’t encounter errors related to missing start month columns from previous months.

Best Regards

[R_K_Damera]

Thanks For Reverting.,,, 

I have Unpivot the data however data is coming as per Alphabetical order. I need matrix as per the input data

Exam : in the above picture we have data from Apr 2023 to Sep 2023

When I do the Unpivot Apr 2023, Aug 2023, jun 2023, July 2023, May 2023, Sept 2023

I need data as per the input format ie.,, Apr 2023, May 2023, Jun  2023, July 2023, Aug 2023, Sept 2023

[Anonymous · Accepted solution]

Hi @R_K_Damera ,

You can refer the following blog to sort the column by custom:

Sort a Column with a Custom Order in Power BI - RADACAD

1. Create a customized sort order dimension table

Quarter Year	Order
Jan 2023	1
Feb 2023	2
Mar 2023	3
Apr 2023	4
...	...

2. Combining the Sort Order Table with your fact table

3. Sort by column

Best Regards