Creating a group index column based on the columns I unpivoted

[gxm204]

Hi, I am using this wholesale customers dataset as an example.

Using PQ, I have unpivoted all the columns from Fresh to Delicassen as shown below. What I would like to do next is create a column "Order ID" that would work like an Index column, except it's incrementing by 1 with each new occurrence of "Fresh" (the first un-pivoted column).

What would be the best way to do this in PQ? Thanks all

 

[sandy666]

maybe

    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 6), type number}})

or if you want Fresh as 1 not 0

    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 6), type number}}),
    #"Added to Column" = Table.TransformColumns(#"Calculated Modulo", {{"Index", each _ + 1, type number}})

 

[gxm204]

I like it! Yeah, using the modulo with the index got me thinking, I could use integer division along with the index to come up with that column, if I start the index at 6. Here is what I came up with, I am dependent on the GUI for my M code so it could be a little clunky.

let
    #"Wholesale customers data" = let
    Source = Csv.Document(File.Contents("C:\Users\GeorgeM\Downloads\Wholesale customers data.csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Channel", Int64.Type}, {"Region", Int64.Type}, {"Fresh", Int64.Type}, {"Milk", Int64.Type}, {"Grocery", Int64.Type}, {"Frozen", Int64.Type}, {"Detergents_Paper", Int64.Type}, {"Delicassen", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Channel", "Region"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 6), type number}}),
    #"Added to Column" = Table.TransformColumns(#"Calculated Modulo", {{"Index", each _ + 1, type number}})
in
    #"Unpivoted Other Columns",
    #"Added Index" = Table.AddIndexColumn(#"Wholesale customers data", "Index", 6, 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 6), Int64.Type)
in
    #"Inserted Integer-Division"

 

[sandy666]

sure, if it works for you that is ok
Have a nice day