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). 

Can anyone help with this? 

Thanks. 

 

Edit: in the original post I forgot to mention that my question is on Power Query, not simple Excel.

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

     

24 Replies

  • 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? 

       

      • 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

    • Idreeesi's avatar
      Idreeesi
      Copper Contributor
      Feb 14, 2022

      Hi Mathetes
      Thank you for trying to help me. However, I couldn't solve the issue using your tips.
      The result I'm looking for is repeating the text value of the cell A1 ("Progress for week 02") in every row in a new column.
      The reason for this is that we receive a weekly report. So, I would like each record to state the week it belongs to. (In the following week, the result should read "Progress for week 03.")

      • mathetes's avatar
        mathetes
        Silver Contributor
        Feb 14, 2022

        Idreeesi 

        The reason for this is that we receive a weekly report. So, I would like each record to state the week it belongs to. (In the following week, the result should read "Progress for week 03."

        Which is a bit different than your first request, which is why what I suggested didn't work. The problem is you're thinking that the "02" and "03" are numbers--which, I'll acknowledge, is what they look like--but in the context of a cell reading "Progress for week 02" those characters are in fact, in Excel, still text.

         

        And that's part of the problem. In order to increment them by 1, we need to

        1. strip the text containing those digits out of their context
        2. convert them to values
        3. increment that value by 1
        4. convert back to text
        5. and then add that text to the words that precede it.

         

        So for that, we'll use a formula that can do all that, but again I'm going to have to make some assumptions. You seem to be saying that the data for each week will fill a column, as opposed to a row. So for the purposes of this creation of a formula that will accomplish your purpose, I'm assuming that our starting point is Cell A2.

        Put this formula in Cell B2 and then copy it across successive columns

        =LEFT(A2,18)&TEXT(VALUE(RIGHT(A2,2)+1),"00")

         

        A spreadsheet is attached containing the formula...

         

        This will work as high as week 100, but after that it will give erroneous results. Let us know if you expect the project to go longer than 100 weeks.

         

        By the way, it's more common to have data bases laid out so that each row, rather than each column, contains the week's data. It's possible for you to do it as you're proposing, but probably in the long run would be more useful if you arrayed each new week's data vertically rather than horizontally.

         

Resources