Creating Index by Row Value

[Mickeybo]

I am importing txt files to Power BI.
The file structure is 

|-> Sales Details Header

Sales Details record

|-> Sales Details Footer

I am trying to add an index to these "Sales Detail Headers", so that I can group by individual Header

I would also like to give the "Sales Detail" an index pertaining to the relating "Sales Details Header".

Do any of you geniuses know how this can be done?

See below for Current vs Desired (in red/blue)

CURRENT

SALES DETAILS HEADER	17905621003763
SALES DETAIL	151188
SALES DETAIL	165201
SALES FOOTER	18473364
SALES DETAILS HEADER	18353152754143
SALES DETAIL	160572
SALES FOOTER	18069906
SALES DETAILS HEADER	17777935985498
SALES DETAIL	172042
SALES DETAIL	163052
SALES DETAIL	154881
SALES DETAIL	161259
SALES DETAIL	170472
SALES FOOTER	17052535

DESIRED

SALES DETAILS HEADER 0001	17905621003763
SALES DETAIL 0001 - 0001	151188
SALES DETAIL 0001 - 0002	165201
SALES FOOTER 0001	18473364
SALES DETAILS HEADER 0002	18353152754143
SALES DETAIL 0002 - 0001	160572
SALES FOOTER 0002	18069906
SALES DETAILS HEADER 0003	17777935985498
SALES DETAIL 0003 - 0001	172042
SALES DETAIL 0003 - 0002	163052
SALES DETAIL 0003 - 0003	154881
SALES DETAIL 0003 - 0004	161259
SALES DETAIL 0003 - 0005	170472
SALES FOOTER 0003	17052535

[mahoneypat · Accepted solution]

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/LCoNADEV/RWbtIs9JshSc0oIgVHfi//9GLd0oTZvtfZzcbSvLMLWlG9s6PKalu7dhbM/SF7QArYQAbJXL3l+db4ciuqdKVQI8Kbd5Xj+tLsZc5St1Jjsro5KpoOTkCmqU90ONgPq3344L1nCVyP83AqGczKC5ouKOeQZJI+eA/NhhB0ZZy76/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Header", each if Text.Contains([Column1], "HEADER") then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Header"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Header"}, {{"AllRows", each _, type table [Column1=nullable text, Column2=nullable text, Header=text]}}),
    AddHeaderIndex = Table.AddIndexColumn(#"Grouped Rows", "HeaderIndex", 1, 1, Int64.Type),
    AddDetailIndex = Table.TransformColumns(AddHeaderIndex, {{"AllRows", each Table.AddIndexColumn(_,"preDetailIndex",0,1), type table }}),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddDetailIndex, "AllRows", {"Column1", "Column2", "preDetailIndex"}, {"Column1", "Column2", "preDetailIndex"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded AllRows", "DetailIndex", each if Text.Contains([Column1], "HEADER") or Text.Contains([Column1], "FOOTER") then null else [preDetailIndex]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"preDetailIndex"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Header", type text}, {"Column1", type text}, {"Column2", type text}, {"HeaderIndex", type text}, {"DetailIndex", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "NewIndex", each if [DetailIndex] <> null then Text.PadStart([HeaderIndex], 4, "0") & "-" & Text.PadStart([DetailIndex], 4, "0") else Text.PadStart([HeaderIndex], 4, "0"), type text)
in
    #"Added Custom2"

Regards,

Pat

[Mickeybo]

That is super Pat...

Got me exactly where I need to... I'll should be able to figure the rest of it out from there.

Thank you so much for the swift response 🙂

[amitchandak]

@Mickeybo , Not very clear,

refer if subgroup index can help

https://www.youtube.com/watch?v=7CqXdSEN2k4