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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mvallee
Frequent Visitor

Combine Excel tables from one column but keeping another column data

Hi,

 

I need to combine Excel files from a Sharepoint folder, these files sometimes having different column names.

So I followed this tutorial : https://www.mssqltips.com/sqlservertip/7182/power-bi-combine-files-column-name-changes-column-count-...

Everything works fine.

My trouble is I need to keep one column in the combined table at the very end, when I call

 

 

= Table.Combine(#"Lignes filtrées"[Personnalisé.Data])

 

 

Here is what I have one step before :

2022-08-12 11_36_26-Clipboard.pngSo I need to keep the info contained in the column named "ProjetDalux" in the resulting table combination.

 

Is it possible ?

 

Thanks in advance for your help.

1 ACCEPTED SOLUTION
MarkLaf
Super User
Super User

I can see how you may want to avoid needing to explicitly call out which columns you want to expand.

One solution: add the [ProjetDalux] text values to your tables in [Personnalisé.Data] and then combine as you were before. Something like:

 

= Table.Combine( 
    List.Transform( 
        Table.ToRecords(#"Lignes filtrées"), 
        (row) => Table.AddColumn( row[Personnalisé.Data], "ProjetDalux", each row[ProjetDalux], type text ) 
    ) 
)

 

Edit: changed from using Table.ToRows to Table.ToRecords as the latter will work regardless of column order.

View solution in original post

6 REPLIES 6
MarkLaf
Super User
Super User

I can see how you may want to avoid needing to explicitly call out which columns you want to expand.

One solution: add the [ProjetDalux] text values to your tables in [Personnalisé.Data] and then combine as you were before. Something like:

 

= Table.Combine( 
    List.Transform( 
        Table.ToRecords(#"Lignes filtrées"), 
        (row) => Table.AddColumn( row[Personnalisé.Data], "ProjetDalux", each row[ProjetDalux], type text ) 
    ) 
)

 

Edit: changed from using Table.ToRows to Table.ToRecords as the latter will work regardless of column order.

Thanks a lot, it works fine. Do you know a way to insert the "ProjetDalux" column at first position ?

Once table is combined, you can reorder using Table.ColumnNames, which you can use to dynamically work with columns as @lbendlin mentioned.

= Table.ReorderColumns( 
    PreviousStep, 
    List.Distinct( {"ProjetDalux"} & Table.ColumnNames( PreviousStep ) ) 
)

For completeness, the following would be the formula for expanding using dynamically generated list of column names. I personally like the former method more, since it retains column types better, at least in the quick testing I did to answer this question.

= Table.ExpandTableColumn(
    Source, 
    "Personnalisé.Data", 
    List.Distinct( List.Combine( 
        List.Transform( Source[Personnalisé.Data], Table.ColumnNames )
    ) ) 
)

 

lbendlin
Super User
Super User

Yes.  Don't use Table.Combine. Instead use Expand Columns.

I can't use "ExpandColumns" as I can't explicitely name the columns to be expanded in advance...

No need to explicitly name the columns.  You can use Table.ColumnNames from a previous step.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.