Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
How can I create a conditional column either in Power query or in dax based on values in multiple columns?
Table1: Exemption data
Student ID | Student Name | English | Maths | Science |
1 | ABC | Yes | No | Yes |
2 | BCD | No | Yes | Yes |
3 | CDE | No | Yes | No |
4 | DEF | Yes | Yes | Yes |
5 | EFG | Yes | No | No |
6 | FGH | No | Yes | No |
Table2: Results Data
Student ID | Student Name | Subject | Marks | Exemption |
1 | ABC | English | 80 | |
1 | ABC | Maths | 75 | |
1 | ABC | Science | 90 | |
2 | BCD | English | 50 | |
2 | BCD | Science | 65 | |
3 | CDE | Maths | 45 | |
3 | CDE | Science | 50 | |
4 | DEF | Maths | 80 | |
5 | EFG | English | 70 | |
5 | EFG | Maths | 95 | |
5 | EFG | English | 100 | |
6 | FGH | Maths | 60 | |
6 | FGH | Science | 70 |
Based on the Student ID in the both tables I created One to Many relationship.
How can I create a column "Exemption" with the values Yes/No based on the data in the Exemption table.
Thank you in Advance.
Solved! Go to Solution.
@Anonymous Couple different ways. You could do this in DAX using a fairly complex calculated column to do LOOKUPVALUE or MAXX(FILTER(...),...). Or, you could unpivot your first table's last three columns, create a composite key of ID and Subject in both tables and then the problem is trivial in DAX or you could even do a Merge at that point in Power Query.
It's easy you can use "and" in the power query
= each if [column_1] = "value1" and [column_2] = "value1" then "Other" else null)
Hello - Here are the steps to follow...
Now, if we view the ResultsTable again (having not made any changes to the ResultsTable), the rest of the matches have populated.
ExemptionsData table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSkanFQNIvH8qM1YlWMgKynZxdwMIKcCUwWWMg29nFFU0WaABI0gTIdHF1Q9GE0GoKZLu6uaPaCtVpBmS6uXtAjIWaqwCVjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, #"English " = _t, Maths = _t, Science = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"English ", type text}, {"Maths", type text}, {"Science", type text}}),
Unpivot = Table.UnpivotOtherColumns(ChangeType, {"Student ID", "Student Name"}, "Attribute", "Exemption"),
TrimmedText = Table.TransformColumns(Unpivot,{{"Student Name", Text.Trim, type text}, {"Attribute", Text.Trim, type text}})
in
TrimmedText
ResultsData table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhYFSrA6ylG9iSUYxkDY3RZMITs5MzUtOBbIsIXqMgEwnZxcU40zRpRC6zCAGGgOZzi6uSDaZoEsg9ECNMwEyXVzdkPRAnW0KstzNHcUJ5uhSMD2Wpjj1GBpANJkB2W7uHkiazNAlEI4DWRQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t, #"Marks " = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Subject", type text}, {"Marks ", Int64.Type}}),
Merge = Table.NestedJoin(ChangeType, {"Student ID", "Student Name", "Subject"}, ExemptionData, {"Student ID", "Student Name", "Attribute"}, "ExemptionData", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "ExemptionData", {"Exemption"}, {"Exemption"})
in
Expand
Hello - Here are the steps to follow...
Now, if we view the ResultsTable again (having not made any changes to the ResultsTable), the rest of the matches have populated.
ExemptionsData table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSkanFQNIvH8qM1YlWMgKynZxdwMIKcCUwWWMg29nFFU0WaABI0gTIdHF1Q9GE0GoKZLu6uaPaCtVpBmS6uXtAjIWaqwCVjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, #"English " = _t, Maths = _t, Science = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"English ", type text}, {"Maths", type text}, {"Science", type text}}),
Unpivot = Table.UnpivotOtherColumns(ChangeType, {"Student ID", "Student Name"}, "Attribute", "Exemption"),
TrimmedText = Table.TransformColumns(Unpivot,{{"Student Name", Text.Trim, type text}, {"Attribute", Text.Trim, type text}})
in
TrimmedText
ResultsData table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhYFSrA6ylG9iSUYxkDY3RZMITs5MzUtOBbIsIXqMgEwnZxcU40zRpRC6zCAGGgOZzi6uSDaZoEsg9ECNMwEyXVzdkPRAnW0KstzNHcUJ5uhSMD2Wpjj1GBpANJkB2W7uHkiazNAlEI4DWRQLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t, #"Marks " = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Subject", type text}, {"Marks ", Int64.Type}}),
Merge = Table.NestedJoin(ChangeType, {"Student ID", "Student Name", "Subject"}, ExemptionData, {"Student ID", "Student Name", "Attribute"}, "ExemptionData", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "ExemptionData", {"Exemption"}, {"Exemption"})
in
Expand
@Anonymous Couple different ways. You could do this in DAX using a fairly complex calculated column to do LOOKUPVALUE or MAXX(FILTER(...),...). Or, you could unpivot your first table's last three columns, create a composite key of ID and Subject in both tables and then the problem is trivial in DAX or you could even do a Merge at that point in Power Query.