Forum Discussion
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?
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 ResultBest Regards,
Bof
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?
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?