Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

CASE WHEN

hello, i have been searching on how to replicate below formula in PBI:   CASE [Org ID] WHEN 10665 THEN 'A' WHEN 3004 THEN 'B' WHEN 9966 THEN 'C' WHEN 11026 THEN 'D' WHEN 100 THEN 'E' WHEN 84 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    You could add conditional column in power query as follows:

    The final output is shown below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMzNVitWJVjI2MDABMywtzczADENDAyMoy8AATFtAVRhCVQAZEM2GxoYWRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org ID", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Org ID] = 10665 then "A" else if [Org ID] = 3004 then "B" else if [Org ID] = 9966 then "C" else if [Org ID] = 11026 then "D" else if [Org ID] = 100 then "E" else if [Org ID] = 84 then "F" else if [Org ID] = 9166 then "G" else if [Org ID] = 9165 then "H" else if [Org ID] = 13182 then "G" else null)
in
    #"Added Conditional Column"

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak is it doable in power query using Custom Column?

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Power Query if then else

     

    if [Org ID] =10665 then "A" else
    if [Org ID] =10665 then "B"  else

    ///// Add other

     3004

     

     

    nested if then else, Take care of datatype, text should be double quote

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak output is showing errors

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous , Try like

         

        if [Org ID] = 10665 then "A" else
        if [Org ID] = 3004 then "B" else
        if [Org ID] = 9966 then "C" else
        if [Org ID] = 11026 then "D" else
        if [Org ID] = 100 then "E" else
        if [Org ID] = 84 then "F" else
        if [Org ID] = 9166 then "G" else
        if [Org ID] = 9165 then "H" else
        if [Org ID] = 13182 then "I" else ""

         

         

        or share the data and error