Forum Discussion

Liam01's avatar
Liam01
Helper I
2 years ago

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...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

 

Then you can filter files:

 

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

 

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:

 

Liam01's avatar
Liam01
Helper I
2 years ago

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:

 

 

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.