Forum Discussion
Column Name Changes Every Month
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.
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.
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
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
5 Replies
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.
Hi Matt,
Thanks for reverting.,,
I have tried however the data is to be shown in Table Visualization instead of Matrix visual
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
