Forum Discussion

soderqvistn's avatar
soderqvistn
Icon for Helper I rankHelper I
6 years ago
Solved

Fill empty row with value from column to the left

Hi,

 

I'd like som help here. I have a hierarchy of my Organisation. Problem in this case is if you look at the top path path 3,4,5 blanks out. And that unfortunately gives a blank row in the slicer aswell. So I was thinking if it would be possible to write an if statement saying. If a column is blank look one to the left. So for path 3 in this case it would write Hus again. Any suggestions?

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi soderqvistn 

     

    In Query Editor

    1. Select [PATH2]
    2. go to Transform > Replace Values and do the following.
    3. The above will create a step "Replaced Value", select it and remove quotes from around "each [PATH1]" like below 
       Table.ReplaceValue(#"Changed Type","", each [PATH1] ,Replacer.ReplaceValue,{"PATH2"})
       
    4. Repeat the process for all other columns [PATH3] replace with [PATH2]

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

7 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi soderqvistn 

     

    In Query Editor

    1. Select [PATH2]
    2. go to Transform > Replace Values and do the following.
    3. The above will create a step "Replaced Value", select it and remove quotes from around "each [PATH1]" like below 
       Table.ReplaceValue(#"Changed Type","", each [PATH1] ,Replacer.ReplaceValue,{"PATH2"})
       
    4. Repeat the process for all other columns [PATH3] replace with [PATH2]

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • soderqvistn's avatar
      soderqvistn
      Icon for Helper I rankHelper I
      6 years ago
      Mariusz wrote:

      Hi soderqvistn 

       

      In Query Editor

      1. Select [PATH2]
      2. go to Transform > Replace Values and do the following.
      3. The above will create a step "Replaced Value", select it and remove quotes from around "each [PATH1]" like below
         Table.ReplaceValue(#"Changed Type","", each [PATH1] ,Replacer.ReplaceValue,{"PATH2"})
         
      4. Repeat the process for all other columns [PATH3] replace with [PATH2]

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      Mariusz Repczynski

       

      Can you explain step 3 a bit mroe detailed?

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi soderqvistn 

         

        Sure, step 2 is replacing the blank value with the text "each [PATH1]" in step 3 you are removing quotes from this text and by that, you are transforming text to M expression "each" is scanning every blank row within your column and replacing it with "[PATH1]" column value.

         

        As a result, for every empty row in column [PATH2] you will get the value of [PATH1].

         

        Let me know if this makes sense for you. 

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski