Forum Discussion

mvallee's avatar
mvallee
Frequent Visitor
4 years ago
Solved

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-b...
Need Help
  • MarkLaf's avatar
    MarkLaf
    4 years ago

    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.

MarkLaf's avatar
MarkLaf
Icon for Super User rankSuper User
4 years ago

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.

mvallee's avatar
mvallee
Frequent Visitor
4 years ago

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

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    4 years ago

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