Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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"
    )

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.