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

Need Help

6 Replies

    • Liam01's avatar
      Liam01
      Helper I
      2 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    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.

    • Liam01's avatar
      Liam01
      Helper I
      2 years ago

      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. 

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

       

       

       

      • 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: