Forum Discussion

FlpsJw's avatar
FlpsJw
Frequent Visitor
2 years ago
Solved

Comparing values of a table column with values of another column

Hello, I'm facing the following issue: In Power Query, I have two tables: Table1, which contains employees and their required monthly trainings, and another table, Table2, which contains the rec...
Need Help
Tips and Tricks
Tutorial Requests
  • Omid_Motamedise's avatar
    Omid_Motamedise
    2 years ago

    If there is such conidtion also, use this one

     

    let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VdJRCikyBJIGhvpAZGRgZALkACXiQcKxOghFRtgVGaEoMsauyBhFEcw6I2KsMyLGOiMM62IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp_ID = _t, Training_ID = _t, Date_Ref = _t, Key = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VdJRCikyBJIG5voGhvpGBkYmQA5QIh4kHKuDpsiSSEWm+BQZgRQZ6RsaoCoyQlFkDFJkom9ggarIGFMRppuAimIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp_ID = _t, Training_ID = _t, DateofTraining = _t, Key = _t]),
    #"Merged Queries" = Table.NestedJoin(Table1, {"Emp_ID", "Training_ID"}, Table2, {"Emp_ID", "Training_ID"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.Max(Table.SelectRows([Table2],(x)=>x[DateofTraining]<_[Date_Ref] ),"DateofTraining"))
in
    #"Added Custom"
Omid_Motamedise's avatar
Omid_Motamedise
Icon for Super User rankSuper User
2 years ago

If there is such conidtion also, use this one

 

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VdJRCikyBJIGhvpAZGRgZALkACXiQcKxOghFRtgVGaEoMsauyBhFEcw6I2KsMyLGOiMM62IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp_ID = _t, Training_ID = _t, Date_Ref = _t, Key = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VdJRCikyBJIG5voGhvpGBkYmQA5QIh4kHKuDpsiSSEWm+BQZgRQZ6RsaoCoyQlFkDFJkom9ggarIGFMRppuAimIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp_ID = _t, Training_ID = _t, DateofTraining = _t, Key = _t]),
    #"Merged Queries" = Table.NestedJoin(Table1, {"Emp_ID", "Training_ID"}, Table2, {"Emp_ID", "Training_ID"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.Max(Table.SelectRows([Table2],(x)=>x[DateofTraining]<_[Date_Ref] ),"DateofTraining"))
in
    #"Added Custom"
FlpsJw's avatar
FlpsJw
Frequent Visitor
1 year ago

The solution worked. But when applied in a table with too many rows, it takes a significant amount of time to load.

 

Thank you!!