Forum Discussion
Custom Column with a particular cell's value (Power Query)
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
rowscolumns 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 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).
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"
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 SergeiBaklan has added the Power Query solution, so we have you covered now with TWO solutions. Enjoy!!
- Yes, I seem fully covered. Many thanks to you and Sergei!
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.
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")
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.
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'.
