Forum Discussion
Replace every row with the first row value
Hello,
I'm loading multiple csv files into PowerQuery.
These are unformatted csv files. Somwhere in the first row is a date field. Before I can delete the first few rows to get to my
actually promoted header, I need to extract that very date value into a extra column. It works well so far with "Add Conditional Column". The problem are the other values below that row, which are different numbers or strings:
| Custom |
| 11.02.2022 |
| sdfdf |
| dfd |
| df |
| dffd |
| dfd |
Now as a sample table, I need to put that date into every row. But "fill down" doesn't work because it only can work with empty or null cells. If I transform that column into date dateype I get this:
| Custom |
| 11.02.2022 |
| Null |
| Error |
| Error |
| Error |
| Error |
| Error |
Fill down also doesn't work with this. How can I write "first value in first row" into every cell below?
Thank you very much in advance.
Best.
Hi Applicable88 ,
Create a new custom column, and enter this as the calculation:
previousStepName{0}[Custom]This will fill the column with the first value in the [Custom] column.
Like this:
Pete
If you prefer, you could transform the existing column instead of creating a new one. Try a conversion to date returning null for values that don't work and then fill down.
= Table.FillDown( Table.TransformColumns( #"Previous Step Name Goes Here", {{"Custom", each try Date.FromText(_) otherwise null, type date}} ), {"Custom"} )
7 Replies
Hi Applicable88 ,
Create a new custom column, and enter this as the calculation:
previousStepName{0}[Custom]This will fill the column with the first value in the [Custom] column.
Like this:
Pete
Hi Anonymous ,
For your scenario, it should just be:
previousStepName{0}[Month]Where 'previousStepName' is the name of whatever Power Query step this linecomes after in the process order.
Pete
If you prefer, you could transform the existing column instead of creating a new one. Try a conversion to date returning null for values that don't work and then fill down.
= Table.FillDown( Table.TransformColumns( #"Previous Step Name Goes Here", {{"Custom", each try Date.FromText(_) otherwise null, type date}} ), {"Custom"} )Hi @Syndicate_Admin ,
thanks a lot for this solution, it works perfectly. I stumbled across a similar yet slightly more complex issue and would like to ask for your support.
In my case, I have three observations per entity and in the first row the column is filled with the person name. In the two subsequent it is filled with null, but should actually be filled with the person name as in the first row above. After three rows, one is again filled with the next person name for which again two blank rows come that should be filled with that persons name. So data looks like below.Is there a way to adapt your solution for this case?
Thanks so much and best wishes
Tobi
Year Person 1 A 2 null 3 null 1 B 2 null 3 null 1 C Syndicate_Admin I think right click on the column and make a "Fill Down" will do the trick.
Best.
Thanks a lot, that did the trick. For anybody else looking to use that, here the official Microsoft post on the function:
Fill values in a column - Power Query | Microsoft Learn
