Replace every row with the first row value

[Applicable88]

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. 

[BA_Pete · Accepted 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

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Applicable88]

@Syndicate_Admin I think right click on the column and make a "Fill Down" will do the trick.

Best. 

[AlexisOlson · Accepted solution]

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"}
  )

[BA_Pete · Accepted 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

[Anonymous]

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?

[BA_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