Forum Discussion

Idreeesi's avatar
Idreeesi
Copper Contributor
Feb 13, 2022
Solved

Custom Column with a particular cell's value (Power Query)

Hi  This should be simple for many people (but not for a newbie like me!). I need to create a new column that contains the value of the top-left cell ("Progress for week 02" in this case).  C...
excel
  • Lorenzo's avatar
    Lorenzo
    Feb 15, 2022

    Hi Idreeesi 

    Re. I need to promote Row # 2 (the one that contains 'Stages') as a header. However, my newly created column ('Custom') gets the title of "week 02" which make it not dynamic (and creates errors on the next steps). I wonder if there is a method whereby I could promote all other rows columns as a header except 'Custom'

     

    One way:

        MyTableBeforePromoting = ...,
    CurrentNames = List.Skip( Table.ColumnNames(MyTableBeforePromoting) ),
    NewNames = List.Skip( Record.ToList( Table.First( Table.Skip(MyTableBeforePromoting) ) ) ),
    RemovedTopRows = Table.Skip(MyTableBeforePromoting, 2),
    RenamedColumns = Table.RenameColumns(RemovedTopRows,
        List.Zip({CurrentNames, NewNames})
    )

    This assumes column [Custom] is the 1st column of table, as shown in your picture

     

mathetes's avatar
mathetes
Silver Contributor
Feb 14, 2022

Idreeesi 

 

Assuming that is cell A2, 

Assuming you really mean the entire column is to contain the value in cell A2

just enter =$A$2 in the top cell in the new column and copy it down as far as the column extends.

 

If you don't mean to copy it to the entire column, but rather want M2 to equal A2, M3 to equal A3, and so forth,

Just enter =A2 and copy it down.

 

And if neither of those fits what you're trying to do, come back with a more complete description of the bigger task and why this (frankly) somewhat odd request.

MOB_Geo's avatar
MOB_Geo
Copper Contributor
Jul 10, 2022

mathetes The initial solution you shared helps partially solve the issue I am currently dealing with in Power Query, but I am wondering if there is a more efficient way to populate the cells in the new column with the desired values instead of dragging down, given that I am working with a data set of almost 5000 rows and there are different values that have to go into the each cell. I have attached a picture to explain. Imagine we wanted to create a column C titled “Status” and the value in each cell has to be the value in the above header cell i.e. either Active, On Hold or Cancelled. How will you go about this in Power Query? 

 

Resources