Marrying Source Data that has multiple record Types in 'm'

Question

I have a text file that has Header rows and Detail Rows. I have created a query that separates them, but now I'm trying to tie the detail rows back to the headers so I can use the data.

I have created an index against the raw data, then created 2 filters, one for Headers and one for details.

Perhaps there is a better method, but what I have done next is to merge the header row [index] back to the Raw text file's [index], with a Header column in the Raw text File showing the line no of the Header index. What I want to do is to get Power Query to fill the null values with the header index, so I can tie the detail rows to it's corresponding header, and then use that column in the raw file to create the final output.

let
// Setup dummy table
    RawData  = Table.FromRecords(
       {
        [RawData="Header A",HeaderLine=1],
        [RawData="Gumph",HeaderLine=null],
         [RawData="Gumph",HeaderLine=null],
        [RawData="Detail A1",HeaderLine=null],
        [RawData="Detail A2",HeaderLine=null],
        [RawData="Detail A3",HeaderLine=null],
        [RawData="Detail A4",HeaderLine=null],
        [RawData="Detail A5",HeaderLine=null],
        [RawData="Header B",HeaderLine=9],
        [RawData="Gumph",HeaderLine=null],
        [RawData="Detail B1",HeaderLine=null],
        [RawData="Detail B2",HeaderLine=null],
        [RawData="Detail B3",HeaderLine=null],
        [RawData="Detail B4",HeaderLine=null],
         [RawData="Header C",HeaderLine=15],
        [RawData="Gumph",HeaderLine=null],
        [RawData="Detail C1",HeaderLine=null],
        [RawData="Detail C2",HeaderLine=null]
       }
         ),
    #"Added Index" = Table.AddIndexColumn(RawData, "Index", 1, 1, Int64.Type),
    // Scan Through and update null HeaderLines with appropriate Headervalues
    Works = Table.ReplaceValue(#"Added Index",each
       [HeaderLine],each if [HeaderLine] = null then
      (RawData[HeaderLine]{[Index]-1}) else [HeaderLine],Replacer.ReplaceValue,{"HeaderLine"}  )
in
    Works

user9930055 user9930055 · Accepted Answer · 2021-07-18T10:04:42

Got answer from BI Community.....

Works = Table.FillDown( #"Added Index" , {"HeaderLine"})

Marrying Source Data that has multiple record Types in 'm'

1 Answers