Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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 )
) )
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |