Forum Discussion
Adapt power query to read sheets from one Excel
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"
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
4 Replies
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
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
