Forum Discussion
Query: Add column at the beginning
To do that you would need to add two additional steps between your data table and reordering like below:
let ... TableData = Table.AddColumn(TransformColumnTypes, "Half Year", each if [Month] < 7 then 1 else 2), ColumnNames = Table.ColumnNames(TableData), ReorderedList = List.Combine({{"Half Year"},List.FirstN(ColumnNames,List.Count(ColumnNames)-1)}),
Result = Table.ReorderColumns(TableData,ReorderedList)
in
Resultwhere
- TableData is the last step of your data, in my case, it is a step where I've added a new custom column
- Half Year is my custom column which I want to be first each time
- ColumnNames is a step which contains a list of columns from TableData
- ReorderedList is a step which represents column names where my custom column comes first and all other column next (does not matter how many)
- Result is a step represents sorted TableData based on ReorderedList
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
To do that you would need to add two additional steps between your data table and reordering like below:
let
...
TableData = Table.AddColumn(TransformColumnTypes, "Half Year", each if [Month] < 7 then 1 else 2),
ColumnNames = Table.ColumnNames(TableData),
ReorderedList = List.Combine({{"Half Year"},List.FirstN(ColumnNames,List.Count(ColumnNames)-1)}),
Result = Table.ReorderColumns(TableData,ReorderedList)
in
Resultwhere
- TableData is the last step of your data, in my case, it is a step where I've added a new custom column
- Half Year is my custom column which I want to be first each time
- ColumnNames is a step which contains a list of columns from TableData
- ReorderedList is a step which represents column names where my custom column comes first and all other column next (does not matter how many)
- Result is a step represents sorted TableData based on ReorderedList
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
- Thank you Ruslan, that was awesome.
Related to the same issue, is there a way to order columns when a column is not at the end.
For example, I want to first move a column to end and then use the process above to sort the columns. Is this possible?It is achievable as well, just need to replace List.FirsN with List.RemoveMatchingItems:
let ... TableData = Table.AddColumn(TransformColumnTypes, "Half Year", each if [Month] < 7 then 1 else 2), ColumnNames = Table.ColumnNames(TableData), ReorderedList = List.Combine({{"Half Year"},List.RemoveMatchingItems(ColumnNames,{"Half Year"})}), Result = Table.ReorderColumns(TableData,ReorderedList) in ResultRegards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!- Awesome, Thanks a lot
Perfect solution to my problem! I keep forgetting that GUI generates steps with static information, which can be replaced with more dyamic code. My issue was insertion of two columns after unknow number of "garbage" columns. Well, they are not really garbage, but they have dates across in the first line and values under each date (pivot). So I had to move 2 newly added columns to the front before promoting headers and unpivoting the dates. Your solutions works perfectly! Thanks.
