Search code examples
appendpowerquerysubtraction

Power Query - Subtract non-consecutive rows and create a new row

I am looking for help with the following problem: My data set contains values for several variables (earnings, opex, volumes) by region and by country. There is a region total for each variable, but not every country is included in the data set, and as a result the sum of the countries does not add up to the region total. I would like to create a new calculated line "All Other Countries" for each variable that equals the sum of values for all non-included countries. The calculation is e.g. Earnings All Other = Earnings Europe - Earnings Germany - Earnings UK - Earnings France.

The solutions I found so far were typically creating new columns for subtractions, and often based on some time-based order that used an index to link a previous row / period with a new row / period. That would not work for my problem, because I want to append the missing country rows to my table and my countries are not sorted.

Below a sample table as the data is now

enter image description here enter image description here

and below the new rows I'd like to add / create

enter image description here enter image description here

Solution

  • This seems to work in PowerQuery. Next time please paste machine readable numbers

    Unpivot

    I assume that when Region=BU, thats the total. So using a custom column to test that

    Filter to get the non-totals and Group. That gives me the sum to subtract

    Filter for the total. Merge in the non-total as a column and subtract the values. Thats the leftover. Change the BU column to say Other and rename some columns. Append to original data

    Re-pivot

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Region", "BU", "Analysis", "IS GRP", "Unit"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "TopLevel", each if [Region]=[BU] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([TopLevel] = 0)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Region", "Analysis", "IS GRP", "Unit", "Attribute"}, {{"Value", each List.Sum([Value]), type number}}),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom", each ([TopLevel] = 1)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows2",{"Region", "Analysis", "IS GRP", "Unit", "Attribute"},#"Grouped Rows",{"Region", "Analysis", "IS GRP", "Unit", "Attribute"},"Filtered Rows2",JoinKind.LeftOuter),
#"Expanded Filtered Rows2" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows2", {"Value"}, {"Value.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Filtered Rows2", "Other.Value", each [Value]-[Value.1]),
#"ChangeNametoOther" = Table.TransformColumns(#"Added Custom1",{{"BU", each "Other", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"ChangeNametoOther",{"Value", "TopLevel", "Value.1"}),
#"Renamed Columns" = #"Unpivoted Other Columns" & Table.RenameColumns(#"Removed Columns",{{"Other.Value", "Value"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Value", List.Sum)
in #"Pivoted Column"

    enter image description here