Indexing by Row Value

[Toonloon]

Hi Folks,

I am importing txt files to Power BI.
The data within is grouped by "Sales Detail Header" lines, coloured in green below.

I am trying to add an index to these "Sales Detail Headers" to group by individual Header and then to show the "Sales Detail".
I am trying to give the "Sales Detail" under each "Sale Detail Header" an index for that particular record.

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

See below for Current vs Desired (in red)

 

[C Moore]

This is probably not the most efficient, but should get where you're going. (Using Excel to create pics)
Add these modified custom functions (a variation on a method courtesy Chris Webb's BI Blog: Nested Calculations In Power Query, yours won't be nested)
a) Add an Index Column that starts at 1.
b) Filter Key for text contains header.
c) Use created function fnRankHeader (paste code into blank query's advanced editor and name)

(tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Index", Order.Ascending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Header Index", 1, 1)
     in
      AddIndex

d) Merge/expand the last step and step before
e) Select column and Fill Down
f) Filter Key by text contains Detail
g) Repeat c) and d) with similar created function fnRankDetail

(tabletorank as table) as table =>
     let
      SortRows = Table.Sort(tabletorank,{{"Index", Order.Ascending}}),
      AddIndex = Table.AddIndexColumn(SortRows, "Detail Index", 1, 1)
     in
      AddIndex

h) Format Indexed like you want
i) Merge columns

 

[Bo_Ry]

Please change the file path D:\Txt.txt.

// Txt
let
    Source = Csv.Document(File.Contents("D:\Txt.txt"),[Delimiter="    ", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Group", each if [Key] = "SALES DETAIL HEADER" then [DETAIL] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Group"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Group"}, {{"Count", each Table.AddIndexColumn(_,"no") }}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Key", "DETAIL", "no"}, {"Key", "DETAIL", "no"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Count", "Custom", each Number.ToText([Index],"0000") & (if [Key] ="SALES DETAIL" then Number.ToText([no]," - 0000") else "" )),
    #"Merged Columns" = Table.CombineColumns(#"Added Custom1",{"Key", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Key"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Key", "DETAIL"})
in
    #"Removed Other Columns"

 

[Toonloon]

Thank you so much for taking the time to look at this.... I'm absolutely thrilled !!!!