Power Query - Replace Values based on parameter

[DW868990]

Hi,

I have a power query question I am looking for some help with - 

I have a fact table with a products column with a list of all products, products are repeated multiple times. 

I have a parameter, when the parameter is set to Yes.....i need the above product column in the fact table, i need values to be replaced by a dimension table column which is just a list like Product 1, Product 2, Product 3 etc.

Not sure if this could be done by a function which you refer to in the m, so when paramater is Yes, refer to the function and replace the values.

Is this, or something similiar possible in power query?

Thanks

[jpessoa8 · Accepted solution]

Hello @DW868990 ,

Yes, it's possible to implement somewhat of an anonymization in PowerQuery.

In the PowerQuery M code, you can decide what is the step that is going to be returned base on some condition.

For instance,

let
    Source = ...,
    ...,
    ...,
    ...,
    Step1 = ...,
    Step2 = ...
in
    if parameter = "Yes" then Step2 else Step1

So, what you could do is, on the query of your factual create all the steps until a full anonymization is achieved and then, on the "in" clause, consider a logic like the above to decide which steps to return.

My suggestion for the anonymization would be:

• A dimensionTable with a column for all products and another column with "Product 1", "Product 2", etc.
• Merge this table with your factual and replace the current Product column with the column from the dimension
• On the "in" clause, return either the step before the merge or the step after the replacement.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa