Joins within a query

Hi yevhen_87 ,

Please try this M code:

let
    // My data source
    Source = Table.FromRecords({
        [period = #date(2024, 1, 9), id = 1, values = 12],
        [period = #date(2024, 1, 9), id = 2, values = 26],
        [period = #date(2024, 1, 9), id = 3, values = 25],
        [period = #date(2024, 1, 9), id = 4, values = 12],
        [period = #date(2024, 1, 10), id = 1, values = 32],
        [period = #date(2024, 1, 10), id = 2, values = 25],
        [period = #date(2024, 1, 10), id = 3, values = 16]
    }),

    // Convert the period column to date type
    ChangeType = Table.TransformColumnTypes(Source, {{"period", type date}}),

    // Find the latest and second latest dates
    DistinctDates = Table.Distinct(Table.SelectColumns(ChangeType, {"period"})),
    SortedDates = Table.Sort(DistinctDates, {{"period", Order.Descending}}),
    LatestDate = SortedDates{0}[period],
    SecondLatestDate = SortedDates{1}[period],

    // Create the table for the latest date
    LatestTable = Table.SelectRows(ChangeType, each [period] = LatestDate),

    // Create the table for the second latest date
    SecondLatestTable = Table.SelectRows(ChangeType, each [period] = SecondLatestDate),

    // Rename columns in both tables to avoid name conflicts
    RenameLatestTable=Table.RenameColumns(LatestTable,{{"values","Current"}}),
    RenameSecondLatestTable=Table.RenameColumns(SecondLatestTable,{{"period","periodTemp"},{"id","idTemp"},{"values","previous"}}),

    // Merge tables
    MergedTable=Table.Join(RenameLatestTable,{"id"},RenameSecondLatestTable,{"idTemp"},JoinKind.LeftOuter),

    Result=Table.RemoveColumns(MergedTable,{"periodTemp","idTemp"})


in
    Result

Best Regards,

Bof