Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey there,
i've got one Excel file with 12 sheets in it for each month of the year. All the sheets have the same structure like Date value in Row 1, Weekday value in Row 2 and the Value I need in Row 3.
I now want to import all the sheets into one query in Power Bi with all date values in column A, weekday values in column B and the needed values in column C.
Do you know how to do this without importing all sheets separately and merging the queries?
Thanks in advance
Solved! Go to Solution.
Hi @AS193
@Vera_33 's solution should work, have you tried that?
Additionally, if your data in Excel Sheet has the first column like below, you can add steps to remove them.
I make a Demo file here. You can change the parameters "folderPath" and "fileName" values to your file's folder and name to check the result.
let
Source = Folder.Files(folderPath),
#"Filtered Rows" = Table.SelectRows(Source, each [Name] = fileName),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
Content = #"Removed Other Columns"{0}[Content],
#"Imported Excel" = Excel.Workbook(Content),
#"Removed Other Columns1" = Table.SelectColumns(#"Imported Excel",{"Data"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.RemoveColumns([Data],"Column1",MissingField.Ignore)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Data", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}, {"Column2", "Weekday"}, {"Column3", "Value"}})
in
#"Renamed Columns"
Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Hi @AS193
@Vera_33 's solution should work, have you tried that?
Additionally, if your data in Excel Sheet has the first column like below, you can add steps to remove them.
I make a Demo file here. You can change the parameters "folderPath" and "fileName" values to your file's folder and name to check the result.
let
Source = Folder.Files(folderPath),
#"Filtered Rows" = Table.SelectRows(Source, each [Name] = fileName),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
Content = #"Removed Other Columns"{0}[Content],
#"Imported Excel" = Excel.Workbook(Content),
#"Removed Other Columns1" = Table.SelectColumns(#"Imported Excel",{"Data"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.RemoveColumns([Data],"Column1",MissingField.Ignore)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Data", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}, {"Column2", "Weekday"}, {"Column3", "Value"}})
in
#"Renamed Columns"
Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Hi @AS193
Agree with @AnkitKukreja to put your file in a folder, but do not click the botton, add custom column instead
let
Source = Folder.Files("C:\Users\******"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Data"}, {"Data"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each Table.Transpose([Data])),
Custom1 = Table.Combine( #"Added Custom1"[Custom])
in
Custom1
Hi @AS193
You can keep your file in a folder and then select import data with "Folder" as a source and then you will be able to see combined file option in place of load and transform.
There select combined and all your sheets will be combined.
Thanks,
Ankit