Dynamic Group index column

[shuyin]

hi,

how can I perform the group index for a repeated value in a row?

Column1	Index
A	1
B	1
C	1
D	1
E	1
A	2
C	2
D	2
B	2
A	3
C	3
D	3
B	3

Thanks,

 

[Bob Phillips]

Home>Transform>Group By, operation of Sum on column Index.

 

[shuyin]

Column 1 is the input and I want to have column index as the result.


Sent from my iPhone using Tapatalk

 

[Rudi]

Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Column1"}, {{"Count", each Table.AddIndexColumn(_, "TC", 1, 1), type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]}[Count]),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TC"}, {"TC"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count", "Index"})
in
    #"Removed Columns1"

 

[shuyin]

This looks ok, however it loses the sequence of column1 at the beginning. We can retain that order by adding an index at beginning.

And I think we can stop at the table.group step then expand. It will give the same result.

Thank you very much.


Sent from my iPhone using Tapatalk

 

[Rudi]

I agree that one really only needs the Source, grouped By and Expanded steps. Removing the others still gets to the same output, but I'm not sure how to retain the original order. The Group By step removes any Index field when grouped. I'm sure there must be some fancy way of manipulating the 'M' to do this, but its out of my knowledge set. Sorry!

 

[MarcelBeug]

You need to group with "All Rows" and then adjust the code to add the Index column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "OriginalSort", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Column1"}, {{"AllData", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"OriginalSort", "Index"}, {"OriginalSort", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllData",{{"OriginalSort", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"OriginalSort"})
in
    #"Removed Columns"

 

[Rudi]

TX Marcel...

I was so close :tongue:

All I needed was to change:

type number

= Table.Group(Source, {"Column1"}, {{"Count", each Table.AddIndexColumn(_, "TC", 1, 1), type number}})

to

type table

= Table.Group(#"Added Index", {"Column1"}, {{"Count", each Table.AddIndexColumn(_, "TC", 1, 1), type table}})

Of course I didn't know one could do this...so I picked up a valuable tip from you! Cheers!!