Forum Discussion

yevhen_87's avatar
yevhen_87
Frequent Visitor
1 year ago
Solved

Joins within a query

Is it possible to left join table on the left with itself to make it look like a table on the right?
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

AlienSx's avatar
AlienSx
Super User
1 year ago

no merge at all

let
    fx = (tbl) => (
        (w) => List.Skip(List.Zip(w & {{null} & List.RemoveLastN(w{2}, 1)}))
    )(Table.ToColumns(Table.Sort(tbl, "period"))),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    group = Table.Group(Source, "id", {"x", fx}),
    result = Table.FromList(List.Combine(group[x]), (x) => x, Table.ColumnNames(Source) & {"previous"})
in
    result
  • yevhen_87's avatar
    yevhen_87
    Frequent Visitor
    1 year ago

    Thanks, but I guess it works not like a left outer join because merges only by id that exists on both periods and what there are periods before given periods will it work?

    • AlienSx's avatar
      AlienSx
      Super User
      1 year ago

      it works withing each id for any number of periods. Any row with id/period gets it's previous value (from previous period with the same id - if it does exist in your data). Rows with no previous value assigned are removed in the end.

      So that if you have n rows (with the same id and different periods) then result will be n - 1 rows. Why don't you add more rows to your data and try it yourself? 

      • yevhen_87's avatar
        yevhen_87
        Frequent Visitor
        1 year ago

        I tried it myself and understood that it removes rows with no previous values but the idea is all from the left and only matching from the right.