Fill down non null values

[Chrisjr]

Hello all, 

I have the following column: 

Name1

5

6

4

Name2

5

9

l would like to fill down the cells with numbers by the name1 and name2. How can I do this. I have tried to change the cells with numbers by blank values and fill them down but this does not work. 

expected result:

Name1

Name1

Name1

Name1

Name2

Name2

Name2

Thanks for the help,

Chris

[Mikelytics · Accepted solution]

Hi @Chrisjr 

Please do the followng in Power Query:

base table

add a custom column

try (if Number.FromText([Column]) > 0  then null else null) otherwise [Column]

*you can also take something like <> 0  if you also have values below 0

Execute the fill

Delete the old column

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Mikelytics · Accepted solution]

Hi @Chrisjr 

Please do the followng in Power Query:

base table

add a custom column

try (if Number.FromText([Column]) > 0  then null else null) otherwise [Column]

*you can also take something like <> 0  if you also have values below 0

Execute the fill

Delete the old column

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Chrisjr]

@Mikelytics thanks for the clear steps

[Chrisjr]

 HI @Arul Thanks for your reply. 

I thought about this solution but how do you replace by null values?

[Arul · Accepted solution]

@Chrisjr , just adding to @Mikelytics  solution. If you want to replace the values and do the fill down in a same column without duplicating and deleting the columns, Please try the below code in your replace value step.

= Table.ReplaceValue(Source,each [Name],
each if Number.FromText([Name]) > 0 then null else [Name],
     Replacer.ReplaceValue,{"Name"})

Thanks,

[Mikelytics]

HI @Arul 

Awesome addon which makes the solution more lean! Will definietely put this in my toolbox 🙂 Thank you!

Best regards

Michael

[Arul]

@Chrisjr ,

Instead of replacing values with blank you can replace values with null and then use fill down. 

Thanks,