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

[Idreeesi]

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.

[#2]

@Idreeesi 

The code in your last picture shows you hard-coded the folder path and the file name (Weekly Progress Week 02.pdf) in fonction File.Contents, hence the result you get...

Look again at the file I shared and replicate:

- Create a Parameter and name it FolderPath with Current Value: Z:\WI\WI Financials\Finance\AR\Invoicing - Power Query\Weekly Invoicing\

- Edit your main query code and use exactly the code I posted in my previous reply

If still not good please upload and share your file (i.e. with OneDrive) + post the link here or attach it to your next reply if you're allowed to do it

[#3]

@L z. 

Thank you so much. This now perfectly fine :)

I just didn't know that the line that starts with 'Source' (highlighted below) is also part of this solution (and thought that the suggested steps end with "// Go:"

Nevertheless, I can't thank you enough for your support. I am left with minor enhancements that I will need to make some research on. I'm glad that the main one has been accomplished:)

[#4]

Glad you have it working now

[#5]

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

 

[#6]

@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