Feb 13 2022 11:53 AM - edited Feb 14 2022 10:44 AM
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.
Feb 16 2022 09:06 PM
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
Feb 17 2022 10:58 AM
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:)
Jul 10 2022 04:22 PM
@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?
Jul 11 2022 05:35 AM
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