Forum Discussion
Creating a new table summarizing rows from other tables
I have looked around for potential solutions, but I cannot find something that applies specifically to my problem. I have tried using CALCULATE(SUM) with different FILTERs to do a quick fix, but it d...
Hi Anonymous
This is the M code for creating Table2. See it all at work in the attached file.
let Source = Table.NestedJoin(Table1, {"Month", "KPI_code"}, Table3, {"Month", "KPI_code"}, "Table3", JoinKind.LeftOuter), #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Employee costs allocation"}, {"Employee costs allocation"}), #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Added employee costs", each [Actual value] + (if [Employee costs allocation] = null then 0 else [Employee costs allocation])*Table.SelectRows(#"Expanded Table3", (inner)=>inner[Month]=[Month] and inner[KPI_code]="P0046")[Actual value]{0}, type number), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Actual value", "Employee costs allocation"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added employee costs", "Actual value"}}) in #"Renamed Columns"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Hi Anonymous
This is the M code for creating Table2. See it all at work in the attached file.
let
Source = Table.NestedJoin(Table1, {"Month", "KPI_code"}, Table3, {"Month", "KPI_code"}, "Table3", JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Employee costs allocation"}, {"Employee costs allocation"}),
#"Added Custom" = Table.AddColumn(#"Expanded Table3", "Added employee costs", each [Actual value] + (if [Employee costs allocation] = null then 0 else [Employee costs allocation])*Table.SelectRows(#"Expanded Table3", (inner)=>inner[Month]=[Month] and inner[KPI_code]="P0046")[Actual value]{0}, type number),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Actual value", "Employee costs allocation"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added employee costs", "Actual value"}})
in
#"Renamed Columns"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
This one did the trick! Thanks!