Index Column Based on Two or More Column Using Power Query

[Winata_Saputra]

Hello everyone. I've been trying to find a solution to my index problem, but still not getting the expected results. Let me explain.

I have 3 important columns (PRODDATE, MACHINE, ITEM TYPE) where index 1 is expected to appear in each different PRODDATE or MACHINE.

I will put the same index and sequentially if PRODDATE, MACHINE, ITEM TYPE are of the same type (like the column index in pic2)

Is this possible?

Please see my pictures for a better understanding.

Raw Data

Desired Result

[Vijay_A_Verma · Accepted solution]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY8xCsAgDEXvklnQfIXasToXuov3v0YVaju0idAhZHj8vPxSiC3DwgFk6Njd0tbWxgdP1YgY8JB46vFJWssys4ZDUMXff8cbSzT3v65SeN/Oj1mgwyxijSrmpJrTX/Ok88BxpVpP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODATE = _t, MACHINE = _t, #"ITEM TYPE" = _t, KG = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODATE", type date}, {"MACHINE", type text}, {"ITEM TYPE", type text}, {"KG", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PRODATE", "MACHINE"}, {{"Temp", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
    fxProcess = (Tbl)=>
        let
            #"Grouped Rows" = Table.Group(Tbl, {"PRODATE", "MACHINE", "ITEM TYPE"}, {{"All", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
            #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
            #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"PRODATE", "MACHINE", "ITEM TYPE"}),
            #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"PRODATE", "MACHINE", "ITEM TYPE", "KG"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG"})
        in
            #"Expanded All",
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"fxProcess"}),
    #"Expanded fxProcess" = Table.ExpandTableColumn(#"Removed Other Columns", "fxProcess", {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"})

in
    #"Expanded fxProcess"

[wdx223_Daniel · Accepted solution]

Record.AddFiled([],y[ITEM TYPE],"")

ah~~,there is a typo, that should be Record.AddField

[wdx223_Daniel]

NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>let a=Text.Format("#[PRODDATE]-#[MACHINE]",y),b=Record.FieldOrDefault(x{1},a,[])&Record.AddFiled([],y[ITEM TYPE],"") in {x{0}&{y&[INDEX=Record.FieldCount(b)]},x{1}&Record.AddField([],a,b)}){0})

[Winata_Saputra]

Hii @wdx223_Daniel is the formula added to the advance editor or to a new column in power query?

[wdx223_Daniel]

click here to add a new step, and replace all the code after equal symbol with my code in the formula area, and delete "NewStep=", then change "PreviousStepName" to your last real step name which can be found in the right pane. 

[Winata_Saputra]

Hii @wdx223_Daniel still error. Can you show me the details please?

[wdx223_Daniel · Accepted solution]

Record.AddFiled([],y[ITEM TYPE],"")

ah~~,there is a typo, that should be Record.AddField

[Winata_Saputra]

Hii @wdx223_Daniel its works, but the result is not same with DESIRED INDEX (picture2). Is there any idea for this solution?
Result from your formulaDesired Index

[wdx223_Daniel · Accepted solution]

= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>let a=Text.Format("#[PRODDATE]-#[MACHINE]",y),b=List.RemoveLastN(Record.FieldOrDefault(x{1},a,{}),each _=y[ITEM TYPE])&{y[ITEM TYPE]} in {x{0}&{y&[INDEX=List.Count(b)]},x{1}&Record.AddField([],a,b)}){0})

[Winata_Saputra]

Hii @wdx223_Daniel it workss!!
Thankyouu

[Vijay_A_Verma · Accepted solution]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY8xCsAgDEXvklnQfIXasToXuov3v0YVaju0idAhZHj8vPxSiC3DwgFk6Njd0tbWxgdP1YgY8JB46vFJWssys4ZDUMXff8cbSzT3v65SeN/Oj1mgwyxijSrmpJrTX/Ok88BxpVpP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PRODATE = _t, MACHINE = _t, #"ITEM TYPE" = _t, KG = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PRODATE", type date}, {"MACHINE", type text}, {"ITEM TYPE", type text}, {"KG", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PRODATE", "MACHINE"}, {{"Temp", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "fxProcess", each fxProcess([Temp])),
    fxProcess = (Tbl)=>
        let
            #"Grouped Rows" = Table.Group(Tbl, {"PRODATE", "MACHINE", "ITEM TYPE"}, {{"All", each _, type table [PRODATE=nullable date, MACHINE=nullable text, ITEM TYPE=nullable text, KG=nullable number]}}, GroupKind.Local),
            #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
            #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"PRODATE", "MACHINE", "ITEM TYPE"}),
            #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"PRODATE", "MACHINE", "ITEM TYPE", "KG"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG"})
        in
            #"Expanded All",
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"fxProcess"}),
    #"Expanded fxProcess" = Table.ExpandTableColumn(#"Removed Other Columns", "fxProcess", {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"}, {"PRODATE", "MACHINE", "ITEM TYPE", "KG", "Index"})

in
    #"Expanded fxProcess"

[Winata_Saputra]

Hii @Vijay_A_Verma

It workss in Power Query!!
Thankyou