Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI Desktop - Power Query - Several variables

Hello all,   I have a question regarding Power Query in Power BI Desktop. I have created a Custom Column with an IF statement and I get the the following, result:   Unit Contract Type Un...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Anonymous ,

    This works using measures, and a visualization, but not yet on a calculated column. 

    As I see it, using the table row as a filter, allows it to work, but not sure yet on how to apply that in a calculated column.

    Am thinking that we have to sum for each Unit, and if > 0 it is a no...

    BTW changed the last row of the table to  a third Unit 0003.

     

  • HotChilli's avatar
    HotChilli
    6 years ago

    I borrowed your logic Nathaniel_C  and came up with a Power Query version.  Thanks for teasing out the requirements.

    Anonymous    please test before adopting this solution

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLFEwMDAwVNJRSs7PKylKTAbxQVxDpVgdnAqMgFwjfAqMgVxjfApMgFwTfApMgVxTFAVGyAoMDY0wTEBTYExIgQmGFcaoCswxfGGCrMDIyBBiQiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Unit = _t, Contract = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit", type text}, {"Contract", type text}, {"Type", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Type] = 1 then 1 else if [Type] = 2 then 1 else if [Type] = 3 then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Unit"}, {{"Any1", each List.Sum([Custom]), type number}, {"all", each _, type table [Unit=text, Contract=text, Type=number, Custom=number]}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Grouped Rows", "Custom2", each if [Any1] > 0 then "No" else "Yes"),
    #"Expanded all" = Table.ExpandTableColumn(#"Added Conditional Column1", "all", {"Contract", "Type", "Custom"}, {"all.Contract", "all.Type", "all.Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded all",{"Any1", "all.Custom"})
in
    #"Removed Columns"

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,

 

"I tried to do this with an IF statement: if [Type] = "1" or "2" or "3" then [Unit] = "No"
else [Unit] = "Yes""

Please clarify. If I look at your last statement, I would expect to see the following:

You are testing on the "Type" column, which you said is set up for the [Contract] column. 

Do you wish to set your test up so that if the [Unit] column is 0001 or 0002 or 0003, then [Available] will read "No", else it will read "Yes"?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Anonymous ,

Is this what you are looking for:

 

Then try this:

 

 

Unless you need the [Type] for some other reason, it is not needed for [Available].

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Nathaniel_C 

     

    Thank you for your reply.

     

    I am looking for what you have presented in the Column.

    However, I do not want to do it manually since there over 10.000 records.

     

    When the column "Type" is 1,2 or 3.  I want all rows for that unit to be "No"

    When there would only be "4" or "5" in the column "Type" e.g. I would like it to be shown as "Yes"

    See the example below (with 2 units)

     

    Adding a Conditional Column would not be sufficient I think, or do I see it wrong?

     

    Once again thank you for your help!


     

     

     

     

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      Anonymous 

      So, if any row in a particular unit has a 1,2 or 3, then all rows for that unit become No in [Available] else they are Yes.

      Nathaniel

      BTW I would make [Type] a text column as you are not doing any math on it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Exactly! that's my problem.

         

        I tried to do this with an IF statement but it did not work for me.

        Maybe an IF statement is not the solution or I did the IF statement wrong.

         

        Thanks for the tip, I will change it in my orginal source :smileyhappy: