List report section headers on each row

domtrump
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.

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

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


Thank you.

-Dom

Comfy
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

let
    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"})
in
    #"Filled Down"
   


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests