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
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?
