locked
Is there a way to do a "sumifs" type of function within the power query editor? RRS feed

  • Question

  • I am trying to create a column in the query editor that sums up sales & volume based on the criteria of month & product group then determine an average price for the group. I have a table(s) below that shows what I would do in excel using sumifs, but I am not sure how to get this to work in power query without loading the table twice, grouping one of the tables by month and product, and merging (this method works but the actual dataset is 20K+ rows so Im not sure if that's the most efficient method). Does anyone have any recommendations on how I can create a custom column that shows my desired output? I tried doing this in a pivot table before, but the users got confused when the subtotal rows did not match the actual sums, therefor I would prefer this calculation to occur in the background. Sorry for the strange formatting, it's not letting me load the picture

    Input

    Prod_Lvl

    Month

    Input

    Prod_Lvl

    Product

    Input

    Prod_Lvl

    Color

    Input

    Prod_Lvl

    Price

    Input

    Prod_Lvl

    Volume

    Input

    Prod_Lvl

    Sales

    Calc

    Group_Lvl

    Volume

    Calc

    Group_Lvl

    Sales

    Calc

    Group_Lvl

    Avg Price

    Output

    Prod v Grp

    Delta Price

    1 Hat Blue 12.00 100.00 1,200.00 480.00 5,980.00 12.46 -0.46
    1 Hat Green 13.00 300.00 3,900.00 480.00 5,980.00 12.46 0.54
    1 Hat Red 11.00 80.00 880.00 480.00 5,980.00 12.46 -1.46
    1 Jacket Blue 50.00 10.00 500.00 360.00 18,600.00 51.67 -1.67
    1 Jacket Green 51.00 100.00 5,100.00 360.00 18,600.00 51.67 -0.67
    1 Jacket Red 52.00 250.00 13,000.00 360.00 18,600.00 51.67 0.33
    1 Shirt Blue 23.00 651.00 14,973.00 917.00 21,343.00 23.27 -0.27
    1 Shirt Green 24.00 210.00 5,040.00 917.00 21,343.00 23.27 0.73
    1 Shirt Red 23.75 56.00 1,330.00 917.00 21,343.00 23.27 0.48
    2 Hat Blue 12.50 250.00 3,125.00 720.00 8,670.00 12.04 0.46
    2 Hat Green 13.50 70.00 945.00 720.00 8,670.00 12.04 1.46
    2 Hat Red 11.50 400.00 4,600.00 720.00 8,670.00 12.04 -0.54
    2 Jacket Blue 51.00 65.00 3,315.00 425.00 22,275.00 52.41 -1.41
    2 Jacket Green 52.00 120.00 6,240.00 425.00 22,275.00 52.41 -0.41
    2 Jacket Red 53.00 240.00 12,720.00 425.00 22,275.00 52.41 0.59
    2 Shirt Blue 25.00 284.00 7,100.00 387.00 9,609.31 24.83 0.17
    2 Shirt Green 24.07 33.00 794.31 387.00 9,609.31 24.83 -0.76
    2 Shirt Red 24.50 70.00 1,715.00 387.00 9,609.31 24.83 -0.33

    Wednesday, November 14, 2018 10:19 PM

Answers

  • For a purely Power Query solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Product", type text}, {"Color", type text}, {"Price", type number}, {"Volume", Int64.Type}}),
        AddedProductSales = Table.AddColumn(ChangedType, "Product Sales", each [Price] * [Volume]),
        AddedGroupedVolume = Table.Group(AddedProductSales, {"Month", "Product"}, {"Table", (i) => Table.AddColumn(i, "Group Volume", each List.Sum(i[Volume])), type table}),
        AddedGroupedSales = Table.TransformColumns(AddedGroupedVolume, {"Table", (i) => Table.AddColumn(i, "Group Sales", each List.Sum(i[Product Sales]))}),
        AddedGroupedAvgPrice = Table.TransformColumns(AddedGroupedSales, {"Table", (i) => Table.AddColumn(i, "Group Avg Price", each [Group Sales]/[Group Volume])}),
        AddedProductVsGroupDelta = Table.TransformColumns(AddedGroupedAvgPrice, {"Table", (i) => Table.AddColumn(i, "Prod vs Grp Delta Price", each [Price] - [Group Avg Price])}),
        CombinedTables = Table.Combine(AddedProductVsGroupDelta[Table])
    in
        CombinedTables

    where Table1 is an Excel table with columns from "Month" to "Volume" (i.e. no calculated columns).


    Column labels used in Table1 are shown in the ChangedType step.
    • Edited by Colin Banfield Thursday, November 15, 2018 6:57 AM Added additional detail
    • Proposed as answer by Imke FeldmannMVP Sunday, November 18, 2018 8:04 AM
    • Marked as answer by Imke FeldmannMVP Sunday, November 25, 2018 6:44 AM
    Thursday, November 15, 2018 6:52 AM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Combination of PP & PQ.
    http://www.mediafire.com/file/eqy7cd3c8u5tkf0/11_14_18.xlsx/file
    http://www.mediafire.com/file/206mob1kpqecg5o/11_14_18.pdf/file

    Thursday, November 15, 2018 3:40 AM
  • For a purely Power Query solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Product", type text}, {"Color", type text}, {"Price", type number}, {"Volume", Int64.Type}}),
        AddedProductSales = Table.AddColumn(ChangedType, "Product Sales", each [Price] * [Volume]),
        AddedGroupedVolume = Table.Group(AddedProductSales, {"Month", "Product"}, {"Table", (i) => Table.AddColumn(i, "Group Volume", each List.Sum(i[Volume])), type table}),
        AddedGroupedSales = Table.TransformColumns(AddedGroupedVolume, {"Table", (i) => Table.AddColumn(i, "Group Sales", each List.Sum(i[Product Sales]))}),
        AddedGroupedAvgPrice = Table.TransformColumns(AddedGroupedSales, {"Table", (i) => Table.AddColumn(i, "Group Avg Price", each [Group Sales]/[Group Volume])}),
        AddedProductVsGroupDelta = Table.TransformColumns(AddedGroupedAvgPrice, {"Table", (i) => Table.AddColumn(i, "Prod vs Grp Delta Price", each [Price] - [Group Avg Price])}),
        CombinedTables = Table.Combine(AddedProductVsGroupDelta[Table])
    in
        CombinedTables

    where Table1 is an Excel table with columns from "Month" to "Volume" (i.e. no calculated columns).


    Column labels used in Table1 are shown in the ChangedType step.
    • Edited by Colin Banfield Thursday, November 15, 2018 6:57 AM Added additional detail
    • Proposed as answer by Imke FeldmannMVP Sunday, November 18, 2018 8:04 AM
    • Marked as answer by Imke FeldmannMVP Sunday, November 25, 2018 6:44 AM
    Thursday, November 15, 2018 6:52 AM