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:

 

UnitContractType
Unit 0001contract 0011
Unit 0001contract 0022
Unit 0001contract 0033
Unit 0001contract 0044
Unit 0001contract 0055

 

This is exactly the result I would like to get.

However, this result is on "Contract Level" and I want to specify on "Unit Level"

What I would like to see is if "Type" = 1,2 or 3 then "No" else "Yes", but then on "Unit Level"

To sum up it would like to see the following result:

 

UnitContractTypeAvailable
Unit 0001contract 0011No
Unit 0001contract 0022No
Unit 0001contract 0033No
Unit 0001contract 0044No
Unit 0001contract 0055No

 

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

 

But that does not seem to work.

How I can solve this?

 

Thanks in advance for the help.

 

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"

     

9 Replies

  • 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!