PQ Add Dynamic "Average" Column

[alishern]

Hello community!

I have a table of reports that gets updated every month. In the PQ editor I pivoted the report date column "REPORT PERIOD" which is in MM-YYYY format. And the data for it I used "Workload Data Input". I did that so it calculate s average of all inputs from each period for each item in the table. But the issue here is that when a new month data is inserted in the table, the average is not recognizing it. Here is the script in the PQ editor:

Pivot:
= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"REPORT PERIOD"]), "REPORT PERIOD", "Workload Data Input", List.Sum)

Inserted Average:
= Table.AddColumn(#"Pivoted Column", "Average", each List.Average({[#"08-2024"], [#"06-2024"], [#"05-2024"], [#"04-2024"], [#"03-2024"], [#"02-2024"], [#"01-2024"], [#"12-2023"], [#"11-2023"], [#"10-2023"], [#"09-2023"], [#"08-2023"], [#"07-2023"], [#"06-2023"]}), type number)

How do I make it dynamic so it picks up forthcoming new month entries?

Thank you very much!

 

[Matt Allington]

The way to avoid this problem, and many other similar problems, is to not pivot the data. Instead you should load data in long tables and keep the date column as an attribute of each row of data. Then, load it to the data model and use DAX measures to do the calculations you need and leverage the pivot table to get the tabulated output you desire.