Forum Discussion
Power Query List
Hi all, could anyone please help with M language to show how to calculate project allocation dynamically when number of projects can be changed in the example provided, e.g. Project 1 allocation = Cost * Project 1 and so on.
Thank you!
DON_DCS Perhaps the attached example helps you on your way. It's a rather simplistic, step by step approach that probably can be made more effective, but then it would most likely be more difficult to follow.
The query unpivots the data first. Then it adds the calculation of the allocation for each row and adds a column to hold allocation column names.
Then it pivots only the columns relevant to the allocations and merges it with the original source, based on the project name. Expand the merged column based on the dynamic list of allocation column names created earlier.
See if you can follow the step and apply them to your own file. If not come back here.
DON_DCS Perhaps the attached example helps you on your way. It's a rather simplistic, step by step approach that probably can be made more effective, but then it would most likely be more difficult to follow.
The query unpivots the data first. Then it adds the calculation of the allocation for each row and adds a column to hold allocation column names.
Then it pivots only the columns relevant to the allocations and merges it with the original source, based on the project name. Expand the merged column based on the dynamic list of allocation column names created earlier.
See if you can follow the step and apply them to your own file. If not come back here.
Riny_van_Eekelen excellent solution. Thank you very much!
To play with code a bit
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], newFieldNames = List.Transform( List.Skip( Table.ColumnNames( Source ), 2), (n) => n & " Allocation" ), addAllocation = Table.AddColumn( Source, "Custom", (r) => Record.FromList( List.Transform( List.Skip( Record.FieldValues(r), 2), (q) => q*Record.FieldValues(r){1} ), newFieldNames ) ), expanded = Table.ExpandRecordColumn( addAllocation, "Custom", newFieldNames) in expandedSergeiBaklan this is amazing, you've used more functions than steps 🙂
I actually prefer your solution. However, can we make it more dynamic, e.g. with the List.Skip(... ,2) there. The condition here is Cost will be the key column before the table can be extended both sides left or right.
That could be like
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], names = Table.ColumnNames( Source ), costPosition = List.PositionOf( names, "Cost" ), newFieldNames = List.Transform( List.Skip( names, costPosition + 1), (n) => n & " Allocation" ), addAllocation = Table.AddColumn( Source, "Custom", (r) => Record.FromList( List.Transform( List.Skip( Record.FieldValues(r), costPosition + 1), (q) => q*Record.FieldValues(r){costPosition} ), newFieldNames ) ), expanded = Table.ExpandRecordColumn( addAllocation, "Custom", newFieldNames) in expandedPlease see in the second sheet attached.
