From DAX to M - return value from one table comparing dates from the two tables and a common ID

Hi NathalieM ,

Based on your description, it seems you're trying to add a new column to your source table by comparing dates and a common ID across two tables. In PowerQuery M, this typically involves merging tables based on your common ID and then applying conditional logic to populate your new column.
First, you'll need to merge your _STAGES table with your 'Niveau Stage par année acad' table based on the Résident_NoRésidant column. In PowerQuery, this is done using the Merge Queries feature.
After merging, you can add a custom column to implement your logic. This involves using the if statement in M to compare the dates and then apply your conditions to populate the "Niv Stage (pour absence)" column.
Here's a basic template to guide you:

let
    // Merge your tables on the common ID
    MergedTable = Table.NestedJoin(_STAGES,{"Résident_NoRésidant"},'Niveau Stage par année acad',{"Résident_NoRésidant"},"NewColumn",JoinKind.LeftOuter),
    // Expand the table to include necessary columns from the second table
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "NewColumn", {"Date fin dernier stage", "Date premier Stage", "NiveauStage"}, {"EndDate", "StartDate", "Level"}),
    // Add a custom column with your logic
    CustomColumn = Table.AddColumn(ExpandedTable, "Niv Stage (pour absence)", each if [NiveauStage] <> 0 then [NiveauStage] else if [DateDébutStage] <= [EndDate] and [DateDébutStage] >= [StartDate] then [Level] else null)
in
    CustomColumn

Please note, this is a simplified example and might need adjustments based on the exact structure of your tables and the full logic you want to implement. If this doesn't help, please provide some sample data and your expected results, thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.