Comparing values of a table column with values of another column

[FlpsJw]

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 records of completed trainings. I linked both tables using a key called EmpID_TrainingID. As a result, Table1 now has a column of tables that brings all trainings of that type completed by the employee.

Now, I want to retrieve the latest date from this table column that is less than the last date of the month of the reference date in the respective row of the table column in Table1.

For example, let's say that my Table1 is like that (already with the merged column):

 Date format (dd/mm/yyyy)

Emp_ID	Training_ID	Date_Ref	Key	ColumnTableFromTable2
235	Tr1	01/01/2024	235_Tr1	[Table]
235	Tr2	01/01/2024	235_Tr2	[Table]
235	Tr3	01/01/2024	235_Tr3	[Table]
235	Tr1	01/02/2024	235_Tr1	[Table]
235	Tr2	01/02/2024	235_Tr2	[Table]
235	Tr3	01/02/2024	235_Tr3	[Table]

And my Table2 is like that

Emp_ID	Training_ID	DateofTraining	Key
235	Tr1	07/01/2024	235_Tr1
235	Tr1	09/01/2024	235_Tr1
235	Tr1	09/05/2024	235_Tr1
235	Tr2	02/10/2024	235_Tr2
235	Tr3	04/08/2024	235_Tr3
235	Tr3	09/01/2024	235_Tr3

On the first row of my Table1 in the ColumTableFromTable2 column the table will show the results for Tr1: 07/01/2024, 09/01/2024 and 09/05/2024, but I just want to get the 09/01/2024 that is the biggest date lower than the Date_Ref value from the Row of Table1.

I need to create a column with this value on Table1. How can I do that?

[v-stephen-msft]

Hi @FlpsJw ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[sbezawada]

Hi @FlpsJw , please folow the below steps to make it in power query

Step-by-Step Guide to Perform in Power Query

1. Load Both Tables into Power Query:

   • In Power BI, go to Home > Transform Data to open Power Query.
   • Load Table1 and Table2 into Power Query.

2. Calculate Max Date_Ref for Each Training_ID in Table1:

   • Select Table1.
   • Go to Group By in the toolbar.
   • In the Group By window, set:
     • Group By: Training_ID
     • New Column Name: MaxDateRef
     • Operation: Max
     • Column: Date_Ref
   • Click OK. This will create a summarized table with each Training_ID and its corresponding MaxDateRef.
   • Rename this table to MaxDateRefTable.

3. Merge MaxDateRefTable with Table2 on Training_ID:

   • Select Table2 in Power Query.
   • Go to Home > Merge Queries.
   • Select MaxDateRefTable as the table to merge with.
   • Use Training_ID as the join key in both tables.
   • Choose a Left Outer Join (default) and click OK.

4. Expand the Merged Table to Include MaxDateRef:

   • In the merged result, click on the expand icon next to MaxDateRefTable.
   • Select only the MaxDateRef column to add it to Table2.
   • Rename the new column to MaxDateRef if needed.

5. Filter Rows to Keep Only DateofTraining Less Than MaxDateRef:

   • With the merged table selected, go to the DateofTraining column.
   • Click the filter icon, select Date Filters > Before.
   • In the filter condition, choose the MaxDateRef column.
   • This filters the rows to keep only those where DateofTraining is less than MaxDateRef.

6. Group the Filtered Table to Get Maximum DateofTraining for Each Training_ID:

   • Go to Group By again.
   • In the Group By window, set:
     • Group By: Training_ID, Emp_ID, Key_Column, MaxDateRef
     • New Column Name: MaxDateOfTraining
     • Operation: Max
     • Column: DateofTraining
   • Click OK. This will create a summary table with the maximum DateofTraining for each Training_ID that is less than MaxDateRef.

7. Load the Result to Power BI:

   • Click Close & Apply to load the resulting table into Power BI.

Final Output Example:

The resulting table will look something like this:

[sbezawada]

Hi @FlpsJw , please try this and reachout to me ,if you face any issues
first get the max date from date_ref in Table_1 and make an inner join with training ID in Table_2 and then filter the table data by max(dateOfTraining) where dateOfTraining less than max(date_ref)

Dax code :
MaxDateOfTrainingLessThanMaxDateRef =
ADDCOLUMNS(
SUMMARIZE(
Table2,
Table2[Training_ID], -- Group by Training_ID
"MaxDateOfTraining",
CALCULATE(
MAX(Table2[DateofTraining]),
Table2[DateofTraining] < MAX(MaxDateForEachTrainingInTable1[MaxDateRef])
)
)
)


output:

Training_ID MaxDateRef MaxDateOfTraining Emp_ID Key_Column

Tr1	2024-01-02	2024-09-01	235	235_Tr1
Tr2	2024-01-02	2024-02-10	235	235_Tr2
Tr3	2024-01-02	2024-04-08	235	235_Tr3

[FlpsJw]

Hey, @sbezawada, I really appreciate your help, but I need to do this on Power Query. I'm sorry 😞

[Omid_Motamedise]

By Table.Last function you can extract the last date. so see the following solution (just copy it and paste in into the advance editor)

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([Table2],"DateofTraining"))
in
    #"Added Custom"

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

[FlpsJw]

But this solution ignores the restriction of being lower than the last date of the month of the value on the column Date_Ref and just brings the max value wich is 09/05/2024. I need to get the 09/01/2024 value.

[Omid_Motamedise · Accepted solution]

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]

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

Thank you!!