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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Liam01
Helper I
Helper I

Combine multiple sheets with the same table but with different columns

Hello all,

 

I'm trying to create a Dataflow to combine multiple Excel spreadsheets where there is a defined table - InputSheet - but the number of columns and the order of the columns is different as over time additional columns have been added.

 

How do I link to this folder, combine these sheets and get all the columns that are in all the sheets?

 

I've tried working back to the 'Removed other columns' step, as i'd seen suggested, and adding any but the ones that are missing don't appear there - I don't know if it's the related to the structure of the Sample File and if something needs doing to that.

 

Thanks

6 REPLIES 6
v-junyant-msft
Community Support
Community Support

Hi @Liam01 ,

As long as the data model (number of columns, column names, data type of each column, etc.) in your Excel tables are roughly the same, you can try to put all excel into the same folder, and then choose to connect to this folder when connecting the data. all excel can be combine.

vjunyantmsft_0-1706670293120.png

Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

If something changes in your data source, you just need to refresh it in Power BI.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks. I've tried that but it doesn't show all the columns, it only appears to show the ones that are on the sample file? Which is the same as when I use the button next to Content. Liam01_0-1706689606495.png

I need to see all the different columns from all the sheets.

 

 

 

@Liam01,

at the beginning backup your code or whole excel file (just for sure).

I recommend you to use Transform Data when you use From Folder

dufoq3_0-1706695688354.png

 

Then you can filter files:

dufoq3_1-1706695735740.png

 

Then in step of combining files do not select sheet directly but use it this way:

dufoq3_2-1706695914365.png

 

Open Transform Sample File in Advanced editor, delete whole code and paste ther this one:

let
    Source = Excel.Workbook(Parameter1, true, true),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.Combine(Source[Data])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"})
in
    #"Removed Other Columns"

 

Now you should be able expand columns:

dufoq3_3-1706696434283.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@dufoq3 

Where you've stated "Then in step of combining files do not select sheet directly but use it this way:" - When I select the 'Parameter' folder, the 'Ok' is greyed out and not able to be selected. I'm only able to select one of the sheets or tables under the 'Parameter' to allow me to continue? When I choose the table I want and put the code it, I get this error:

Liam01_0-1706713429180.png

 

 

To add, i'm doing this in Power Query for Power BI, I don't know if it makes a difference but my understanding is there's differences between PQ in BI and Excel. Also the files I'm wanting are on Sharepoint.

dufoq3
Super User
Super User

Hi @Liam01,
Sheet1 (Table1)

dufoq3_0-1706638528676.png

 

Sheet2 (Table2) - different column order and 1 additional column (Salary)

dufoq3_1-1706638570672.png

 

Result

dufoq3_2-1706638601546.png

 

You can download excel file here.

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thanks. I'm not sure what the sheet is meant to be showing - I can only see Table 1 and Table 2 on it? And it won't let me open in the app

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors