Selecting a row as header

[acerNZ]

Hi Experts

I have this excel and similare excel with 100s of coloumns and and with many merged rows on the top, I need to get the a specific row where actual data start in each sheet,  

So I explored and got this link and I think, I followed but I might have made mistake

Please can you help me . 

Selecting specific row as header  solution by @edhans 

let
    Source = Excel.Workbook(File.Contents("C:\Data\Academic\PowerBI\Promoting_header\promoteheader.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Filtered for header row" = Table.SelectRows(#"Changed Type", each ([Data] = "Header Row")),
    #"Appended Query" = Table.Combine({#"Filtered for header row", #"Changed Type"}),
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"East", type text}, {"West", type text}, {"Central", type text}, {"Mountain", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Section 1] <> null)),
    #"Filtered out header row" = Table.SelectRows(#"Promoted Headers", each ([Header Row] <> "Header Row"))
in
    #"Filtered Rows"

Please can you help me understand this. Currently I get expresion error: The Column "East" of the table is not found.

2. If I have 100s of columns, do I have to mention each coloumn in the #ChangedType

[Jimmy801 · Accepted solution]

Hello @acerNZ 

with this function you are reading your whole worksheet. In your case you have to skip the first 3 lines and then promote the headers. Here the code

let
    Source = Excel.Workbook(File.Contents("YourFile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    TableSkip = Table.Skip(Sheet1_Sheet, 3),
    #"Promoted Headers" = Table.PromoteHeaders(TableSkip, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"East", type any}, {"West", type any}, {"Central", type any}, {"Mountain", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}})
in
    #"Changed Type"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[acerNZ]

@edhans Thank you for your time and help, I tested  your solution as well and it really works. Cheers

So we have 2 solutions which work perfectly. I will follow your blog @edhans .. thanks a ton

[edhans]

Great @acerNZ. Glad to be of assistance. Thanks for following the blog. 😁

[edhans · Accepted solution]

Hi @acerNZ ,

You didn't specify, but if the first column always has "East" in it, you can use this method:

let
    Source = Excel.Workbook(File.Contents("C:\Users\User Path\Downloads\promoteheader.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    FindFirstRow =
        List.PositionOf(
            Sheet1_Sheet[Column1],
            "East"
        ),
    SkipToFirstRow = 
        Table.Skip(Sheet1_Sheet, FindFirstRow)
in
    SkipToFirstRow

It uses the logic and steps in this blog I wrote last week.

The above code will put your East, West, etc. as the first row. Then you can do the Promote to Header and continue with your transformations.

If your data always just has 3 bogus rows in the top, there is no need to find the row East is on. Just use the Remove Top Rows feature in the home ribbon, and type 3 for the rows to skip.

[Jimmy801 · Accepted solution]

Hello @acerNZ 

with this function you are reading your whole worksheet. In your case you have to skip the first 3 lines and then promote the headers. Here the code

let
    Source = Excel.Workbook(File.Contents("YourFile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    TableSkip = Table.Skip(Sheet1_Sheet, 3),
    #"Promoted Headers" = Table.PromoteHeaders(TableSkip, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"East", type any}, {"West", type any}, {"Central", type any}, {"Mountain", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}})
in
    #"Changed Type"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[acerNZ]

Thank you Jimmy 

Worked like charm, sorry for late testing

[acerNZ]

Hey @PhilipTreacy Thanks a lot. I always struggle to upload any file here, I don't get that option and I tried again, I used github to publish, Please can you confirm, if you can see this or I will use google drive etc.

https://github.com/acerNZ/PowerBI 

Please can you confirm, if you can access this as this is my first time, I try this way.

[PhilipTreacy]

Hi @acerNZ 

Please supply one of the Excelworkbooks you are trying to transform.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.