Conditional Replace instead of a New Conditional Column

Power Query
1

Hi All,
I have a dataset with some missing data- simplified version below:
I’m trying to populate the missing ID values for Category Other1 and Other2 which I know can be done in a new column using IF or SWITCH statement.

Is it possible to conditionally set or change the value of the ID column in Power Query Editor without creating a new column? If so, can you please point me in the right direction on how to do this?

image

Thanks,
DJ

1 Like
2

@Deanj,

In Power Query, you can use the Replace Values command to do this. I wouldn’t recommend it if you have a lot of changes to make, but for a few it’s a handy way to accomplish just what you’re looking to do.

image

Here’s the code that command creates:

image
image933×38 2.89 KB

If you want to build in more complex conditional statements without adding a new column, you can go into the advanced editor and edit that code to include an IF statement(s).

  • Brian
1 Like
3

Thanks Brian for your response. Yes, this is what I’m trying to do. The replace must be conditional. Not sure of the exact syntax to include if statement in advanced editor, but will give it a shot.

1 Like
4

@DeanJ ,

Give it a shot, and if you run into problems you know where to find us… :grinning:

  • Brian
1 Like
5

Hi,
In addition to @BrianJ
You can try to add conditional column in Power query and remove the base column before loading it back to report .

Thanks,
Anurag

6

@Anurag ,

Thanks - good point. I thought of that as well - certainly a bit easier to implement, but I know that some of the folks in this situation have a strict constraint that they’re not allowed to add any columns or tables to the data model, even temporary ones. But I should have mentioned that in case @DeanJ doesn’t have that strict constraint.

  • Brian
7

@BrianJ @Anurag I can add a column, but am trying to solve this without adding a separate column

8

Hi @DeanJ,

As far as I know there is no way to do a conditional replace through the UI except for maybe creating a bunch of Replace Value steps (like @BrianJ already mentioned).

But it can be done with a bit of coding. Copy this script into a new blank query.

let
    SubstitutionRecord = [ Other1 = 3, Other2 = 4 ],
    Source = Table.FromColumns( { {"Books", "Magazines", "Other1", "Other2"}, {1, 2, null, null} }, { "Category", "ID" } ),
    TransformColumn = Table.ReplaceValue( Source, each [ID], each Record.FieldOrDefault( SubstitutionRecord, [Category], [ID] ), Replacer.ReplaceValue, {"ID"} )
in
    TransformColumn

.
What is happening?

  1. SubstitutionRecord is a manually created record that contains all your conditional replacements
  2. TransformColumn is replacing each value in the ID column with the SubstitutionRecord value if that’s present otherwise with itself.

Here’s a link to the documentation on Record.FieldOrDefault

I hope this is helpful.

2 Likes
9

@Melissa,

Amazing! When I first replied, I thought this would be a simple conditional based on ReplaceValue. I ended up working on it for a long time last night and couldn’t get this to work.

Let’s definitely put this one on the list for collaborative video solutions.

  • Brian
10

Hi @DeanJ, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

11

Hi @DeanJ, we’ve noticed that no response has been received from you since September 10.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

12

Hi @DeanJ, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.