Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

2 ACCEPTED SOLUTIONS

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.

 

IF Unit 4.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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"

 

View solution in original post

9 REPLIES 9
Nathaniel_C
Super User
Super User

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:

IF Unir.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Anonymous ,

Is this what you are looking for:IF Unit 2.PNG

 

Then try this:

 

IF Unit 3.PNG

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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!


Example.PNG

 

 

 

 

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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 Smiley Happy

Hi @Anonymous , 

Work project - will post as soon as I can.

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

IF Unit 4.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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"

 

Anonymous
Not applicable

@HotChilli and @Nathaniel_C 

 

Thank you for your help! 

 

I have tested both solutions and they both work for me, but  I will add the Conditional Column in my orginal source (for future analysis).

 

I really needed this last part to be able to finish my report, you guys are the best!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors