Selecting a row as header

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

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.