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 14 2022 07:46 AM
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.
Feb 14 2022 08:08 AM - edited Feb 14 2022 08:11 AM
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.")
Feb 14 2022 08:29 AM - edited Feb 14 2022 08:32 AM
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
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.
Feb 14 2022 10:42 AM
Feb 14 2022 11:09 AM
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"
Feb 14 2022 11:26 AM
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):
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:
I hope this clarifies what I'm looking for, and sorry - once again - for the confusion.
Feb 14 2022 11:42 AM
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.
Feb 14 2022 11:43 AM
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")
Feb 14 2022 11:46 AM
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!!
Feb 14 2022 08:01 PM
Thank you Sergei
this worked perfectly fine. However, I encountered another problem when I promoted the header.
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'.
Feb 14 2022 08:02 PM
Feb 14 2022 08:56 PM
SolutionHi @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
Feb 15 2022 01:29 AM
Feb 15 2022 03:23 AM
Glad I could help. A less complex approach starting with your Source table:
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
Feb 15 2022 11:38 AM
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?
Feb 16 2022 02:18 AM
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:
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
Feb 16 2022 07:14 AM
Feb 16 2022 10:13 AM
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"]
)
...
Feb 16 2022 07:53 PM
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).
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).
Lastly, I'm sharing the steps I have followed (as per your advice) in he Advanced Editor:
Please let me know where I went wrong, and many thanks for your continued support.
Feb 14 2022 08:56 PM
SolutionHi @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