How can one group index number by a custom category in this image show below. The challenge is on the left section, while the expected output is shown by an arrow at the right section. I want for for every ID that has row containing G-TAN, should have a seperate index number, so also K-TAN, M-TAN according to this format as shown in the image below:

Thank you.

CodePudding user response：

try

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"FIKA"}, {{"Count", each _, type table}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1001, 1000, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Table.AddIndexColumn([Count],"ID2", [Index], 1, Int64.Type)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count", "Index"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"DOSE", "UNIT", "QTY", "ID2"}, {"DOSE", "UNIT", "QTY", "ID2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"ID2", "ID"}})
in  #"Renamed Columns"