Forum Discussion

Chrisjr's avatar
Chrisjr
Helper IV
3 years ago
Solved

Fill down non null values

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

Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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

     

     

     

  • Arul's avatar
    Arul
    3 years ago

    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,

6 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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

     

     

     

  • Arul's avatar
    Arul
    Super User
    3 years ago

    Chrisjr ,

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

    Thanks,

  • Chrisjr's avatar
    Chrisjr
    Helper IV
    3 years ago

     HI Arul Thanks for your reply. 

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

    • Arul's avatar
      Arul
      Super User
      3 years ago

      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's avatar
        Mikelytics
        Resident Rockstar
        3 years ago

        HI Arul 

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

         

        Best regards

        Michael