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