none
Grouping in power query RRS feed

  • Question

  • I have the following data, i want to group by product name and pack size so all prices from supplier are in 1 row for the same product.

    Product_Name Pack_Qty 1 2 3 4 5 6 7 8
    ABILIFY 10MG ORODISP TAB 28 28 null null 95.56 null null null 30.62 null
    ABILIFY 10MG ORODISP TAB 28 28 null null null 38.4 null null null null
    ABILIFY 10MG ORODISP TAB 28 28 null 87.96 null null null null null 35.5
    ABILIFY 10MG TABS 28 28 null null 95.56 null null null 23.06 null
    ABILIFY 10MG TABS 28 28 null 79.93 null null 26 null null 25.93
    ABILIFY 10MG TABS 28 28 null null null null null 26.35 null null
    ABILIFY 10MG TABS 28 28 null null null 29.99 null null null null
    ABILIFY 10MG TABS 28 28 null null 37.42 null null null null null
    ABILIFY 15MG ORODISP TAB 28 28 null null 95.56 null null null 78.89 null
    ABILIFY 15MG TABS 28 28 null null 95.56 null null null 23.81 null
    ABILIFY 15MG TABS 28 28 null 83.95 null null 25 null null 21.05
    ABILIFY 15MG TABS 28 28 null null null 20.99 null null null null
    ABILIFY 15MG TABS 28 28 null null 95.07 null null null null null
    ABILIFY 1MG/1ML ORAL SOLN 150ML 150 null null 102.39 null null null 20.14 null
    ABILIFY 1MG/1ML ORAL SOLN 150ML 150 null null null null null 30.5 null null
    ABILIFY 1MG/1ML ORAL SOLN 150ML 150 null null null 25.49 null null null null
    ABILIFY 1MG/1ML ORAL SOLN 150ML 150 null null 28.06 null null null null null
    ABILIFY 1MG/1ML ORAL SOLN 150ML 150 null 82.75 null null 26 null null 85.59
    ABILIFY 30MG TABS 28 28 null null 191.12 null null null 77.75 null
    ABILIFY 30MG TABS 28 28 null null null 79.95 null null null null
    ABILIFY 5MG TABS 28 28 null null 95.56 null null null 21.1 null
    ABILIFY 5MG TABS 28 28 null 86.48 null null null null null 21.5
    ABILIFY 5MG TABS 28 28 null null null null null 20.4 null null
    ABILIFY 5MG TABS 28 28 null null null 20.39 null null null null
    ABILIFY 5MG TABS 28 28 null null 25.71 null null null null null
    ABILIFY MAINTENA POW+SOLV PR INJ 400MG VIAL 1 1 null null 219.31 null null null null null
    ABILIFY MAINTENA POWD+SOLV PR 400MG P/F SYRINGE SUSP FOR INJ 1 1 null null 219.31 null null null null null
    ABILIFY SOLN FOR INJ 9.75MG/1.3ML VIAL 1 1 null null 3.41 null null null null null

    Tuesday, September 17, 2019 3:28 PM

All replies

  • Hi,

    The following example code assumes that the data is in an Excel Table named Table1.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            Source,
            {
                {"Product_Name", type text}, 
                {"Pack_Qty", Int64.Type}, 
                {"1", type number}, 
                {"2", type number}, 
                {"3", type number}, 
                {"4", type number}, 
                {"5", type number}, 
                {"6", type number}, 
                {"7", type number}, 
                {"8", type number}
            }
        ),
        groupedRows = Table.Group(
            changedType, 
            {"Product_Name", "Pack_Qty"}, 
            {
                "Tables", 
                each Table.FirstN(Table.FillUp(_, List.Skip(Table.ColumnNames(_), 2)), 1), 
                type table
            }
        ),
        combinedTables = Table.Combine(groupedRows[Tables])
    in
        combinedTables

    Tuesday, September 17, 2019 5:14 PM
  • Thank you but I need something simpler as I will be adding new suppliers with prices. I previously managed to do a fillup function but I have forgotten what it was
    Thursday, September 19, 2019 4:32 PM
  • Thank you but I need something simpler as I will be adding new suppliers with prices. I previously managed to do a fillup function but I have forgotten what it was

    I don't know how adding new suppliers changes anything. I've added several test rows to the original list you provided and the code worked fine with the new test suppliers.

    As far as the code is concerned, it doesn't get any simpler. Pretty much a single step (groupedRows) is doing all the heavy lifting. If you find that step too complicated, I'd have to assume that you're new to Power Query.

    Thursday, September 19, 2019 5:43 PM
  • Hi 1234AN. Power query does have both "fill up" and "fill down", if that's what you're looking for.

    Ehren

    Tuesday, September 24, 2019 9:27 PM
    Owner
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    In your data there are four instances where there are two prices
    for the same product from the same vendor.
    I assumed they are price adjustments at a given date.
    Added simplified dates of 1 and 2.
    Since your data appears to be a PQ Query result,
    I generated a simplified, editable raw data Table1.
    With dates added, a more useful output seems to be a PP PivotTable.
    http://www.mediafire.com/file/57t6rcdz0cxip4b/09_24_19.xlsx/file
    http://www.mediafire.com/file/2b4i2ghutvrz5zr/09_24_19.pdf/file

    Wednesday, September 25, 2019 2:42 AM