Creating custom column if specific values from two other column are expected result

[Justas4478]

Hi, I have two columns storage and destination.
And there are many reasons for stock being moved from storage to destination.
Depending on storage and destination it can have different description.
I tried to use create from example column but unfortuantelly it does not work for my case since there are to many variations.
I can only think of couple ways on how to get me result.
One is to create addiotional column from combining storage and destination columns and then do 'column from example' on that new column.
Other is to write e query that could do if storage is 'X' and destination is 'Y' then description would be 'Z'.

Any one know of any good solution that could work.
Keep in mind that on top of combinations that are in screenshot there could be more added in the future.
Thanks

[v-xianjtan-msft · Accepted solution]

Hi @Justas4478 

Thank you rajendraongole1 for the great reply. If this has helped you solve your problem, please consider accepting rajendraongole1's reply as a solution.

Here are some of my suggestions that I hope will help you.
1. Write dynamic rules in the Power Query editor using the M language. Example:

= Table.AddColumn(
    PreviousStep,
    "Description",
    each if [Storage] = "X" and [Destination] = "Y" then "Z"
         else if [Storage] = "A" and [Destination] = "B" then "C"
         else "Default Description"
)

2. If the combinations are very large and may change dynamically, it is recommended to maintain an external mapping table (e.g. Excel or database) with storage, destination and corresponding description. Then import the mapping table into Power BI. Use the MERGE function to join the master table to the mapping table and find the description based on storage and destination.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @Justas4478  - create a new calculated column as below 

Description_c =

SWITCH (

    TRUE (),

    AND ( [Storage] = "G5B0", [Destination] = "G5B1" ), "G5 - Bulk to Bulk",

    AND ( [Storage] = "G2B0", [Destination] = "G2B1" ), "G2 - Bulk to Bulk",

    AND ( [Storage] = "G1B0", [Destination] = "G1B1" ), "G1 - Bulk to Bulk",

    AND ( [Storage] = "G3B0", [Destination] = "G3P0" ), "G3 - Gate 3 Transfer",

    AND ( [Storage] = "G2B1", [Destination] = "G2P3" ), "G2 - Replen (G2P3)",

    "Unknown"

)