Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Winata_Saputra
Frequent Visitor

Index Column Based on Two or More Column Using Power Query

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 DataRaw Data

 

Desired ResultDesired Result

 

3 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

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

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

View solution in original post

= 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})

View solution in original post

10 REPLIES 10
wdx223_Daniel
Super User
Super User

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})

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

wdx223_Daniel_0-1678953500206.png

 

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. 

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

Picture5.png

 

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

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

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 formulaResult from your formulaDesired IndexDesired Index

= 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})

Hii @wdx223_Daniel it workss!!
Thankyouu

Vijay_A_Verma
Super User
Super User

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"

Hii @Vijay_A_Verma

It workss in Power Query!!
Thankyou

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors