Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AS193
New Member

Import and concatenate tables

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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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. 

021801.jpg

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.

021802.jpg

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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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. 

021801.jpg

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.

021802.jpg

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.

Vera_33
Resident Rockstar
Resident Rockstar

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

AnkitKukreja
Super User
Super User

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

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors