Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soderqvistn
Helper I
Helper I

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?

 

Skärmklipp.PNG

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @soderqvistn 

 

In Query Editor

  1. Select [PATH2]
  2. go to Transform > Replace Values and do the following.image.png
  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

 

View solution in original post

7 REPLIES 7
Mariusz
Community Champion
Community Champion

Hi @soderqvistn 

 

In Query Editor

  1. Select [PATH2]
  2. go to Transform > Replace Values and do the following.image.png
  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

 


@Mariusz wrote:

Hi @soderqvistn 

 

In Query Editor

  1. Select [PATH2]
  2. go to Transform > Replace Values and do the following.image.png
  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?

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

 

Yeah that I understood sorry 🙂 But where and how do I perform step 3?

Sorry @soderqvistn 

 

Select Step Created "Replaced Values" in Applied steps.

You can adjust it in the formula bar as on the screenshot below.
image.png

 
Please see the attached file for the reference.
 
Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski



I didnt have formula view ticked in thats why I didn't find it. But now I get this error message.. Any ideas?Skärmklipp2.PNG

Hi @soderqvistn 

#"Changed Type" is a previous step in my example, in yours it can be something else.

Try following the steps from my original reply using UI to replace the values and later just remove the quotes.

 

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.