Adapt power query to read sheets from one Excel

[Anonymous]

Hello,
I made Power Query read from a directory, because i know it goes to each Excel and reads their sheets. But know i was trying to adapt it so that it reads one excel file, lets call it test.xlsx, from a filepath parameter i've created, lets call it path_test.
How can i adapt this code to do that? I was trying to adapt unsucessfully.

Thanks

let
    Source = Folder.Files("H:\aaa\mmm\Test"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    Content = #"Removed Other Columns"{0}[Content],
    #"Imported Excel" = Excel.Workbook(Content),
    #"Removed Columns" = Table.RemoveColumns(#"Imported Excel",{"Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"IT-xxx", "Perimeter"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Perimeter name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Cluster Job] <> "Cluster Job")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"I", "Int"}, {"E", "Ext"}})
in
    #"Renamed Columns1"

[Jimmy801 · Accepted solution]

Hello @Anonymous 

adapt the code like this

let
    #"Imported Excel" = Excel.Workbook(File.Contents(path_test)),
    A_Sheet = #"Imported Excel"{[Item="YourSheetName",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(A_Sheet, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"IT-xxx", "Perimeter"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Perimeter name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Cluster Job] <> "Cluster Job")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"I", "Int"}, {"E", "Ext"}})
in
    #"Renamed Columns1"

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

[Anonymous]

I had an error using this changed code, but starting from this was ok ! 🙂

= Excel.Workbook(File.Contents(FilePath))

[PhilipTreacy]

Hi @Anonymous 

Change your Source step to this

Source = Folder.Files(path_test),

I don't know if you will need to adjust the steps after this, it'll depend on the data/structure of the test.xlsx file.  If it's the same as the file structure when you first created this query, it should work ok.

Regards

Phil

[Anonymous]

It add's an extra slash "\" to path_test (which already is path\test.xlsx)