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-bi-combine-files-column-name-changes-column-count-changes/

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 :

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

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.

6 Replies

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

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

    • mvallee's avatar
      mvallee
      Frequent Visitor
      4 years ago

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

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

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

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