Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Proud to be a Datanaut!
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"}
)
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
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.
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 @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
Proud to be a Datanaut!
Hello Pete,
it seems like your answer is the solution to my problem.
But unfortunate it doesn't work in my case.
I need a new column where every row takes the value from first row column month.
Could you pls have a look at it and advice?
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.