February 1, 2019
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
February 1, 2019
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
Trusted Members
Moderators
November 1, 2018
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)}))
February 1, 2019
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
Trusted Members
Moderators
November 1, 2018
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
Answers Post
1 Guest(s)