Hi,
i have table1:
Col1Col2
a | b |
and query where i am creating additional column.
How to reorder automatically and move added column to the beginning of table?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"AddColumn" = Table.AddColumn(Source, "Col3", each "")
in
#"AddColumn"
Best,
Jacek
Hi Jacek,
After adding the column, if you drag it into position so it's the first column, a new step is created which will do this every time.
Regards
Phil
Thank you Philip.
The issue is that it will only work to this one particular table.
Imagine that you are adding this colun to 5 others with different number of columns.
How to make this automatic in the M language?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"AddColumn" = Table.AddColumn(Source, "Col3", each ""),
#"Reordered Columns" = Table.ReorderColumns(AddColumn,{"Col3", "Col1", "Col2"})
in
#"Reordered Columns"
So Col1, and Col2 in this case can change...
Best,
Jacek
There are probably simpler solutions, but you could get a list of the column names, remove the last one (the new column you added) and then combine that with the new column name like this:
= Table.ReorderColumns(AddColumn,List.Combine({{"Col3"}, List.RemoveLastN(Table.ColumnNames(AddColumn), 1)}))
thank you it worked.
but i have another issue.
Sometimes Col3 can be not last one but next-to-last and RemoveLastN will not work.
I tried with:
= Table.ReorderColumns(AddColumn,List.Combine({{"Col3"}, List.RemoveMatchingItems(Table.ColumnNames(AddColumn), "Col3")}))
but i am getting error:
"We cannot convert the value "Col3" to type List.
How to avoid this?
Query:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"AddColumn" = Table.AddColumn(Source, "Col3", each ""),
#"Added Custom" = Table.AddColumn(AddColumn, "Col4", each 1),
LastStep = Table.ReorderColumns(AddColumn,List.Combine({{"Col3"}, List.RemoveMatchingItems(Table.ColumnNames(AddColumn), "Col3")}))
in
LastStep
Best,
Jacek
It's expecting a list of items to remove so use {"Col3"} instead of just "Col3":
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"AddColumn" = Table.AddColumn(Source, "Col3", each ""),
#"Added Custom" = Table.AddColumn(AddColumn, "Col4", each 1),
LastStep = Table.ReorderColumns(AddColumn,List.Combine({{"Col3"}, List.RemoveMatchingItems(Table.ColumnNames(AddColumn), {"Col3"})}))
in
LastStep
Thank you!
Best,
Jacek