Combine Excel tables from one column but keeping another column data

[mvallee]

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 :

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.

[MarkLaf · Accepted solution]

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]

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

[MarkLaf]

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]

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

[mvallee]

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

[lbendlin]

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