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

[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

[Franz_Osaka_]

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 @Franz_Osaka_ ,

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