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
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.")
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
- strip the text containing those digits out of their context
- convert them to values
- increment that value by 1
- convert back to text
- 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).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!!