cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

Vera_33
Solution Sage
Solution Sage

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
Resolver III
Resolver III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.