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

Conditional Column Based on the Values In Multiple Columns

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 IDStudent NameEnglish MathsScience
1ABCYesNoYes
2BCDNo YesYes
3CDENo YesNo
4DEFYesYesYes
5EFGYesNoNo
6FGHNo  Yes No

 

Table2: Results Data

Student IDStudent NameSubjectMarks Exemption
1ABCEnglish      80 
1ABCMaths      75 
1ABCScience      90 
2BCDEnglish      50 
2BCDScience      65 
3CDEMaths      45 
3CDEScience      50 
4DEFMaths      80 
5EFGEnglish      70 
5EFGMaths      95 
5EFGEnglish    100 
6FGHMaths     60 
6FGHScience     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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
hephitao
Regular Visitor

It's easy you can use "and" in the power query 

 

= each if [column_1] = "value1" and [column_2] = "value1" then "Other" else null)
Syndicate_Admin
Administrator
Administrator

Hello - Here are the steps to follow...

 

  • ExemptionsData - select all columns except the subjects, then Transform (ribbon) > Unpivot Other Columns.  This will transpose the subjects and exemptions from columns to rows.  Note, Power Query assigned default names for the new columns as Attribute and Value. 
    • jennratten_0-1629724232658.png
  • The column of data you want to add to the results table is Value and you will likely want that column to be named Exemption.  Intead of having to rename the column after the tables have been merged, save yourself a step later by editing the script to make the new column name Exemption instead of Value.
    • Script generated by Power Query:
    • jennratten_5-1629725415093.png

       

    • New, edited script:
    • jennratten_6-1629725483461.png

       

  • Merge the two tables (either as a new table or join the Exemptions Data to the Results Data) with the join keys being Student ID, Student Name and Subject/Attribute.  Be sure to select them in the same order on both tables (hold down the ctrl button and click the columns on the first table, repeat for the second table).  Leave the join kind set to the default.  Note - I have removed the Exemption column from the sample results table, since we will be adding a new column named Exemption as opposed to filling/replacing values in an existing column.
    • jennratten_7-1629725574350.png

       

  • Now that the tables are merged, you have a new column (new column name defaults to the name of the second table).  Each row in that column contains a table of matching results from the second table (these are sometimes referred to as nested tables).  Click the whitespace next to one of the Table values to see a preview of the results.
    • jennratten_8-1629725712861.png

       

  • Click the icon in the new column header and select the columns from each nested table that should be added to the primary table.  In this case, we only want "Exemption".
    • jennratten_9-1629725770414.png

       

  • Now you have the exemption results (yes/no) in a column.
    • jennratten_10-1629725865605.png

       

  • The values in the Exemption column that = null did not have any matches.
  • Why didn't Power Query find a match in the ExemptionsData table for Student ID 1 / Student Name ABC / Subject English - it appears to be present in the ExemptionsData table?
    • The reason is because they are not exactly the same.  In the ResultsTable, if we select English and highlight the the subject in data preview, we can see that the value is "English".
      • jennratten_11-1629726139125.png

         

    • However, when doing the same in the ExemptionsData table, we see that the value actually has an extra space at the end "English ", therefore they are not the same.
      • jennratten_12-1629726241286.png

         

    • Things like this can be prevented by cleaning up the data prior to performing transformations that rely on matching datapoints.
      • For example, on the ExemptionsData table, you can select the Student Name and Attribute columns and then trim the values.
      • jennratten_13-1629726595955.png
      • Now, if we view the ResultsTable again (having not made any changes to the ResultsTable), the rest of the matches have populated.

      •  

        jennratten_14-1629726683308.png

         

 

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

 

jennratten
Super User
Super User

Hello - Here are the steps to follow...

 

  • ExemptionsData - select all columns except the subjects, then Transform (ribbon) > Unpivot Other Columns.  This will transpose the subjects and exemptions from columns to rows.  Note, Power Query assigned default names for the new columns as Attribute and Value. 
    • jennratten_0-1629724232658.png
  • The column of data you want to add to the results table is Value and you will likely want that column to be named Exemption.  Intead of having to rename the column after the tables have been merged, save yourself a step later by editing the script to make the new column name Exemption instead of Value.
    • Script generated by Power Query:
    • jennratten_5-1629725415093.png

       

    • New, edited script:
    • jennratten_6-1629725483461.png

       

  • Merge the two tables (either as a new table or join the Exemptions Data to the Results Data) with the join keys being Student ID, Student Name and Subject/Attribute.  Be sure to select them in the same order on both tables (hold down the ctrl button and click the columns on the first table, repeat for the second table).  Leave the join kind set to the default.  Note - I have removed the Exemption column from the sample results table, since we will be adding a new column named Exemption as opposed to filling/replacing values in an existing column.
    • jennratten_7-1629725574350.png

       

  • Now that the tables are merged, you have a new column (new column name defaults to the name of the second table).  Each row in that column contains a table of matching results from the second table (these are sometimes referred to as nested tables).  Click the whitespace next to one of the Table values to see a preview of the results.
    • jennratten_8-1629725712861.png

       

  • Click the icon in the new column header and select the columns from each nested table that should be added to the primary table.  In this case, we only want "Exemption".
    • jennratten_9-1629725770414.png

       

  • Now you have the exemption results (yes/no) in a column.
    • jennratten_10-1629725865605.png

       

  • The values in the Exemption column that = null did not have any matches.
  • Why didn't Power Query find a match in the ExemptionsData table for Student ID 1 / Student Name ABC / Subject English - it appears to be present in the ExemptionsData table?
    • The reason is because they are not exactly the same.  In the ResultsTable, if we select English and highlight the the subject in data preview, we can see that the value is "English".
      • jennratten_11-1629726139125.png

         

    • However, when doing the same in the ExemptionsData table, we see that the value actually has an extra space at the end "English ", therefore they are not the same.
      • jennratten_12-1629726241286.png

         

    • Things like this can be prevented by cleaning up the data prior to performing transformations that rely on matching datapoints.
      • For example, on the ExemptionsData table, you can select the Student Name and Attribute columns and then trim the values.
      • jennratten_13-1629726595955.png
      • Now, if we view the ResultsTable again (having not made any changes to the ResultsTable), the rest of the matches have populated.

      •  

        jennratten_14-1629726683308.png

         

 

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

 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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