Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Unit 0001 | contract 001 | 1 |
Unit 0001 | contract 002 | 2 |
Unit 0001 | contract 003 | 3 |
Unit 0001 | contract 004 | 4 |
Unit 0001 | contract 005 | 5 |
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:
Unit | Contract | Type | Available |
Unit 0001 | contract 001 | 1 | No |
Unit 0001 | contract 002 | 2 | No |
Unit 0001 | contract 003 | 3 | No |
Unit 0001 | contract 004 | 4 | No |
Unit 0001 | contract 005 | 5 | No |
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.
Solved! Go to Solution.
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.
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"
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
Proud to be a Super User!
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
Proud to be a Super User!
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!
@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.
Proud to be a Super User!
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
Hi @Anonymous ,
Work project - will post as soon as I can.
Nathaniel
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.
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"
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.