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.