Log In
Register
Home
February 1, 2019
Offline
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
See Answer
October 5, 2010
Offline
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
February 1, 2019
Offline
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
Offline
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
Offline
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
Offline
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)
