List report section headers on each row

Posts: 1
Joined: Sat Apr 29, 2017 4:01 am

List report section headers on each row

Postby domtrump » Sat Apr 29, 2017 7:26 am

I have a fixed length text file that I want to transform using power query. Each section has the name of the group above it, then the column headers and then the detail data. How can I get the name of the group to show up as the first column of each record?

I attached two images. One of the raw report and one of the desired table.

report fixed header 1.PNG
report fixed header 1.PNG (6.87 KiB) Viewed 695 times

report fixed header 2.PNG
report fixed header 2.PNG (7.45 KiB) Viewed 695 times

Thank you.


Posts: 1
Joined: Wed May 03, 2017 1:06 am

Re: List report section headers on each row

Postby Comfy » Mon May 08, 2017 6:04 pm

Use a custom column with an if statement to Identify the Operator Code row. Then fill down.

Code: Select all

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "OP", each if Text.Contains([Operator], "Operator Code:") then Text.Trim(Text.Replace([Operator], "Operator Code:","")) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"OP"})
    #"Filled Down"

Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 3 guests