Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Gangula
Advocate II
Advocate II

Query: Add column at the beginning

When we use the add column function in power query, it adds the new column at the very last. Is there a way to create it at the very beginning?

I don't want to move it to the beginning using the right click option because my column headers might change.

1 ACCEPTED SOLUTION
zoloturu
Memorable Member
Memorable Member

@Gangula,

 

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
Result

where

 

- 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!

View solution in original post

8 REPLIES 8
zoloturu
Memorable Member
Memorable Member

@Gangula,

 

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
Result

where

 

- 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!

Anonymous
Not applicable

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.

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?

@Gangula,

 

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
    Result

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Awesome, Thanks a lot
Greg_Deckler
Super User
Super User

Add your column. Demote your headers. Reorder. Promote your headers.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I haven't tried this yet, but wouldn't it be a problem if the total number of columns in my source increase?

Yep, could be. Maybe @ImkeF has a different suggestion.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.