Creating a visual that includes changing column names

[Cyh-SGPT]

Dear community, 

Greetings from Singapore.

I am facing an issue when I tried to create a table view for my data, it seemed I have to update my filter every month.  

I am trying to build a table to consolidate company's sales forecasting by qty for all products(indicated by Item No.), that involves files from several business units(BU). BU sales will update the files monthly, and every month they will add a new month column on the source data(format: Month in No./1/YYYY).

I wish to get the visual to load the latest 9 months' data(9 month from now, so I will expect to view forecast from 7/1/2024 - 3/1/2025, and the next month from 8/1/2024 - 4/1/2025). I already filtered only the latest 9 months' data from Power Query as:

 = Table.SelectColumns(#"Removed Columns",List.Combine({{"Item No.", "BU"}, List.LastN(Table.ColumnNames(#"Removed Columns"),9)}))

For the above visual created, since I choose the column name by hard code, such as "7/1/2024", when I update the "4/1/2025" data in my source data, "7/1/2024" column will be removed, and my visual will show error, since the columns cannot be found; I have to manually adjust the visual so that the visual can be shown normally. 

I wonder if there is a way to automatically choose the column names? Since the format of the column name is fixed.

Any help is appreciated, thank you!

[Anonymous]

Hi @Cyh-SGPT 

Is your problem solved, if so, please mark the corresponding reply as a solution, which will help users with the same problem to better solve their problem.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HoangHugo]

Hi, in this case, I think I will put all your "Month" columns into one common column, by using unpivot/pivot column funtion in Query Editor. Then you can easy put filter top 9 latest months on common column.