Forum Discussion

Chrisjr's avatar
Chrisjr
Helper IV
2 years ago
Solved

reorder columns dynamically

I have the following table:

 

BrandVersion01/202302/202304/202306/202307/2023
AA1CV50080089045608045
AB5CVnullnull75048909650
AC1CV300null89079087980

 

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 : 

BrandVersion01/202302/202304/202306/202307/202308/2023
AA4CVnull1805008008908090
ABFDnull505750489096508075
ACFD10980300null8908090

 

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_Daniel's avatar
    wdx223_Daniel
    Community Champion

    = Table.AddColumn(#"changetype", "Column4", each "Price")

    = Table.ReorderColumns(#"Added Custom",List.InsertRange(Table.ColumnNames(#"changetype"),2,{"Column4"}),MissingField.Ignore)