Conditional Column Based on the Values In Multiple Columns

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. 
  • 
• 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:
  • 

     

  • New, edited script:
  • 

     

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

     

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

     

• 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".
  
  • 

     

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

     

• 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".
    • 

       

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

       

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

    • 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

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

• 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. 
  • 
• 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:
  • 

     

  • New, edited script:
  • 

     

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

     

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

     

• 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".
  
  • 

     

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

     

• 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".
    • 

       

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

       

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

    • 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