Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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
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
I had an error using this changed code, but starting from this was ok ! 🙂
= Excel.Workbook(File.Contents(FilePath))
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
Proud to be a Super User!
It add's an extra slash "\" to path_test (which already is path\test.xlsx)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.