Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Merge Queries: Columns with a true match AND a false match

Hello everyone,

 

I have two tables filled with two different kind of tasks (e.g. table X and Y), and some columns brings the start date and finish date. So, from the subtraction of dates I have the duration, which I transform into a function List.Dates. This list is expanded to new rows.

 

What I'm aiming to do is to find the percentage of those days where I have two concomitants tasks assigned to the same resource, and put those two on the same row to compare information about it. For this, I perform a merge queries just like the figure below (column #2 of the merge on table y is hidden). It works fine, as it compares those dates where the resource is the same and finds the match.

 

 

The new goal is to find a match of dates and resources inside the table X (fraction of the records below). So, in summary I want to find a solution that identifies records where matches column [ListDates] and [Equipe] but with different value of [Task ID].

 

 

Thank you in advance for your time.

 

Need Help
Tips and Tricks
  • jennratten's avatar
    jennratten
    5 years ago

    Hello - this is how you could accomplish this...

     

    In this example, I have two tables: Table1 and Table2.  I would like create a new table (Table3) with records in which the Student ID and Student Name match, but Attribute/Subject do not.

     

    Table1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUBWZGqxUqxOtJIRkO3k7AIkg5MzU/OSU5HkjIFsZxdXIOmbWJJRjCRjAmS7uLphkTEF2eLmjtUuMyDbzd0DVZeCUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Attribute = _t, Exemption = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Attribute", type text}, {"Exemption", type text}})
in
    #"Changed Type"

     

    Table2:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhYFSrE60khGQ6eTsAiSDkzNT85JTgSwzU7CUMZDp7OIKJH0TSzKKgbQJRMIEyHRxdUOSgBpmCrLBzR3FHnOIlBmQ6ebuASQ9MotL8osqoVKxAA==", 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}})
in
    ChangeType

     

    Table3:

    let
    Source = Table2,
    Merge = Table.NestedJoin(Source, {"Student ID", "Student Name"}, Table1, {"Student ID", "Student Name"}, "ExemptionData", JoinKind.LeftOuter),
    FilterSubtables = Table.SelectRows ( Merge, each [Subject] <> [ExemptionData][Attribute]{0})
in
    FilterSubtables

     

3 Replies

  • jennratten's avatar
    jennratten
    Super User
    5 years ago

    Hello - this is how you could accomplish this...

     

    In this example, I have two tables: Table1 and Table2.  I would like create a new table (Table3) with records in which the Student ID and Student Name match, but Attribute/Subject do not.

     

    Table1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUBWZGqxUqxOtJIRkO3k7AIkg5MzU/OSU5HkjIFsZxdXIOmbWJJRjCRjAmS7uLphkTEF2eLmjtUuMyDbzd0DVZeCUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Attribute = _t, Exemption = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Attribute", type text}, {"Exemption", type text}})
in
    #"Changed Type"

     

    Table2:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhYFSrE60khGQ6eTsAiSDkzNT85JTgSwzU7CUMZDp7OIKJH0TSzKKgbQJRMIEyHRxdUOSgBpmCrLBzR3FHnOIlBmQ6ebuASQ9MotL8osqoVKxAA==", 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}})
in
    ChangeType

     

    Table3:

    let
    Source = Table2,
    Merge = Table.NestedJoin(Source, {"Student ID", "Student Name"}, Table1, {"Student ID", "Student Name"}, "ExemptionData", JoinKind.LeftOuter),
    FilterSubtables = Table.SelectRows ( Merge, each [Subject] <> [ExemptionData][Attribute]{0})
in
    FilterSubtables

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi jennratten,

       

      Thank you for your help, you've done it smoothly, I appreciate it.

       

      I've changed the last part to instead of filtering rows, I added a new column with a if statement to convert the "all-matches" to null values.

       

       

      let
    Source = Table2,
    Merge = Table.NestedJoin(Source, {"Student ID", "Student Name"}, Table1, {"Student ID", "Student Name"}, "ExemptionData", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Merge, "Custom", each if [Subject] <> [ExemptionData][Attribute]{0} then [ExemptionData] else null),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Student ID", "Student Name", "Attribute", "Exemption"}, {"Custom.Student ID", "Custom.Student Name", "Custom.Attribute", "Custom.Exemption"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ExemptionData"})
in
    #"Removed Columns"

       

       

      Thus, I have now exactly what I needed. Thank you again.