Headers to Column - Power Query

[koaoak_SN]

Hey there!

I have a data sets from 43 locations that I am uploading from Excel. Each report has the same format for each column / column header along with Headers at the top of the report showing the report parameters (location, dates of the report, etc.).

In Power Query, I need to figure out how to take the location header and apply it to each row in a new column. When I have uploaded all 43 reports, they are being appended together, so the location for each property needs to be added as a column to differentiate location data. After this is done, the "junk" headers will be deleted.

Below is a screen shot from the scenario accomplished in Excel just in case a visual is better. 

[Anonymous · Accepted solution]

Hi @koaoak_SN ,

1. Suppose I have a table like this.

let
    Source = Excel.Workbook(File.Contents("Sample\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Added Custom" = Table.AddColumn(Sheet1_Sheet, "Tag", each Table.FirstValue(Table.SelectColumns(Sheet1_Sheet,"Column3"))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type any}, {"Column2", type text}, {"xxx-xxx", type text}, {"xxx-xxx_1", type text}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"aa", Int64.Type}, {"bb", type text}, {"cc", type text}, {"xxx-xxx", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"xxx-xxx", "Tag"}})
in
    #"Renamed Columns"

Repeat steps for each table.

2. It may be easier to add a label column to the data source.

Tip: Posting the power query question to the corresponding forum will get a faster and more professional solution.

Power Query - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data