Forum Discussion
reorder columns dynamically
I have the following table:
| Brand | Version | 01/2023 | 02/2023 | 04/2023 | 06/2023 | 07/2023 |
| AA | 1CV | 500 | 800 | 890 | 4560 | 8045 |
| AB | 5CV | null | null | 750 | 4890 | 9650 |
| AC | 1CV | 300 | null | 890 | 7908 | 7980 |
In my transfmoration steps , I need to add a custom columns then move it just after the column Category but this has to be dynamic as same transformations steps will be apply to other files.
The column's names for "Brand" and "Category" will never change.
The number of columns can change.
So what I did is:
= Table.AddColumn(#"changetype", "Column4", each "Price")
= Table.ReorderColumns(#"Added Custom",{"Brand", "Version", "Column4", "01/2023", "02/2023", "04/2023", "06/2023", "07/2023"},MissingField.Ignore)
Now , I want to apply the same steps on the below table :
| Brand | Version | 01/2023 | 02/2023 | 04/2023 | 06/2023 | 07/2023 | 08/2023 |
| AA | 4CV | null | 180 | 500 | 800 | 890 | 8090 |
| AB | FD | null | 505 | 750 | 4890 | 9650 | 8075 |
| AC | FD | 10 | 980 | 300 | null | 890 | 8090 |
However, the issue I get is the following : after the ReorderColumns step, the last column is "07/2023" instead of being "08/2023".
How can I reorder the colums in a dynamic way so that extra columns will not be moved before the "07/2023" column?
Many thanks
= Table.AddColumn(#"changetype", "Column4", each "Price")
= Table.ReorderColumns(#"Added Custom",List.InsertRange(Table.ColumnNames(#"changetype"),2,{"Column4"}),MissingField.Ignore)
2 Replies
- wdx223_DanielCommunity Champion
= Table.AddColumn(#"changetype", "Column4", each "Price")
= Table.ReorderColumns(#"Added Custom",List.InsertRange(Table.ColumnNames(#"changetype"),2,{"Column4"}),MissingField.Ignore)
- ChrisjrHelper IV
many thanks wdx223_Daniel 🙌