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

     

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? 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 11, 2022

MOB_Geo 

As variant

let
    Source = Excel.CurrentWorkbook(){[Name="range"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(
        Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(
        #"Promoted Headers",
        "Status",
        each
            if [Location] = null
            then [Project Name]
            else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Status"}),
    #"Filtered Rows" = Table.SelectRows(
        #"Filled Down",
        each ([Location] <> null))
in
    #"Filtered Rows"

for

Resources