SOLVED

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

Copper Contributor

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

Idreeesi_0-1644848176800.png

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.

24 Replies

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

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

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

 

Dear Mathetes,
Thank you so much (I learned new thing in Excel). However, I'm terribly sorry for the mistake; I forgot to mention in my post that I am working on Power Query (perhaps that's why you felt my post was a bit strange).

@Idreeesi 

 

No, Power Query (or its absence) had nothing to do with how the first inquiry seemed odd. Read it carefully: 

I need to create a new column that contains the value of the top-left cell ("Progress for week 02" in this case). 

You simply say that the new column has to contain the value of the top left cell. Nothing about changing it. Just containing it. Full Stop. The answer I gave you did exactly that. And it was that which was "odd"

@mathetes 

I acknowledge that English is not my first language! However, I still think that why I said describes what I was looking for (I just didn't feel like including a lot of rows).
My data looks like this (with a few more columns):

Idreeesi_0-1644866547381.png

 

I need the value of the top-left cell ("Progress for week 02") to repeat on every row (i.e. to replace "Stage 2", "Stage 3", etc...)

 

The desired output looks as follows:

Idreeesi_1-1644866760335.png

 

I hope this clarifies what I'm looking for, and sorry - once again - for the confusion. 

@Idreeesi 

You may add week No column as

...
    WeekNo = Text.Replace( PrevStep[Column1]{0}, "Progress for ", ""),
    AddWeekColumn = Table.AddColumn(PrevStep, "Week No.", each WeekNo),
...

after filter Column 1 on texts starts with "Stage " and rename column.

@Idreeesi 

 

I can appreciate the difficulty of having some other language as your primary (you are good enough with English that I'd never have guessed). There is some confusion in what you've described in prior posts, in that it seemed you were looking for that progression to be in new columns. Now it appears that it's desired in new rows in the same column.

 

The same formula does it, as shown in the attached spreadsheet, modified so that the increments take place in succeeding rows of column A.

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

mathetes_0-1644867694278.png

 

@Idreeesi 

 

And to the extent that Power Query is a factor, I do my work in the Mac environment, where we don't have Power Query. I see that my friend @Sergei Baklan has added the Power Query solution, so we have you covered now with TWO solutions. Enjoy!!

@Sergei Baklan 

Thank you Sergei
this worked perfectly fine. However, I encountered another problem when I promoted the header. 

Idreeesi_0-1644897459108.png

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 as a header except 'Custom'.  

Yes, I seem fully covered. Many thanks to you and Sergei!
best response confirmed by Idreeesi (Copper Contributor)
Solution

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

 

Thank you so much. I'm sure this will work (I will try it at home, as I don't seem to have the input files in my office laptop).
Also, thanks for the correction, 'columns' is correct (rows was a typo).

@Idreeesi 

 

Glad I could help. A less complex approach starting with your Source table:

SourceTable.png

where I need the value of the top-left cell ("Progress for week 02") to repeat on every row

    Source = ...,
    WeekNum = "week " & Text.AfterDelimiter(
        Table.FirstValue(Source), // Top-left cell of the Source table
        "week"
    ),
    RemovedTopRow = Table.Skip(Source),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRow, [PromoteAllScalars=true]),
    AddedWeekNo = Table.AddColumn(PromotedHeaders, "Week No", each WeekNum),
    ReorderedColumns = Table.SelectColumns(AddedWeekNo,
        {"Week No"} & List.RemoveItems(Table.ColumnNames(AddedWeekNo), {"Week No"})
    )

Corresponding sample attached

@L z. 

Thank you again. This worked like a charm! I'm sure my colleagues will be as impressed as I am :)

One last (cosmetic!) step at the 'Source' step (at the very start of the query). What is the easiest way for allowing the users to choose which PDF (i.e. weekly report) to choose? Can we do this through a 'Parameter' or any other method? 

Idreeesi_0-1644953679889.png

 

Hi @Idreeesi 

 

Doable in principle. However, this raises some first questions:
1 - Where will you get your PDF from (OneDrive business, SharePoint, Folder on a local HD/Server...)?
2 - Will you and your colleagues use the same Folder path to the PDFs?

 

In principle, this requires an additional query that returns a list of the PDFs available in a given Folder. Then the user would select a file name in that list. This could look like this with 2 parameters on an Excel sheet:

 

Screenshot.png

 

This would be reduced to 1 parameter if you and your colleagues use the same Folder path in which case you could hard-code it in the additional query

Thank you. This is truly insightful. Would it be possible for you to share a link where I can put this into practice?
Answering your question, my colleagues and I are saving these PDF files in a share folder, and we all have the same file path. So 1 parameter should work.
Looking forward to your advice. Thanks.

@Idreeesi 

In the attached file you'll find 2 queries:

- FolderPath: update it with the path the folder where your PDFs are stored. Don't forget the \ at the end

- PdfInFolder: this query auto-refresh when the Excel file opens (so it'll faill when you open it 1st time as it currently refers to my local folder). The query loads to a sheet (can be hidden later)

 

In the Name Manager:

- FileList: refers to the PdfInFolder table. Used to populate the drop-down list in Sheet1!C2

- SelectedFileName: refers to Sheet1!C2 and you'll use it at the beginning of your existing as follow:

 

let
    // Get File name from Excel named range "SelectedFileName":
    FileName = Table.FirstValue(
        Excel.CurrentWorkbook(){[Name="SelectedFileName"]}[Content]
    ),
    // Concat. Folder path, file name and pdf extension:
    FullFileName = FolderPath & FileName & ".pdf",
    // Go:
    Source = Pdf.Tables(File.Contents(FullFileName),
        [Implementation="1.3"]
    )
    ...

 

@L z. 

Thank you for your help. I manage to make the drop down list. However, I couldn't run it in the query. The screenshot below shows that I have chosen "Week 1" from the drop down list, but the table still shows Week 2 (I think the issue is with the 'FirstValue' function at the start of the query).

Idreeesi_0-1645069541475.png

This shows that the weeks are available (are are being updated on refresh). Note: this means that I can't hide the sheet as suggested, as the team need to refresh this sheet to be able to see the new week's report (PDF file).

Idreeesi_1-1645069574455.png

 

Lastly, I'm sharing the steps I have followed (as per your advice) in he Advanced Editor: 

Idreeesi_3-1645069952125.png

Please let me know where I went wrong, and many thanks for your continued support.

 

1 best response

Accepted Solutions
best response confirmed by Idreeesi (Copper Contributor)
Solution

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

 

View solution in original post