March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Is it possible to left join table on the left with itself to make it look like a table on the right?
Solved! Go to Solution.
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
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
You can use table.Group. Just copy and past the below code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUM7DUMzIwMlHSUTIEYSOlWB00cSMQNsMUNwaJm2KKm6CYY2iAbL4xFnEjFHMQ4iDzDYH2xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [period = _t, id = _t, values = _t]),
#"Grouped Rows" = Table.Group(Source, {"id"}, {{"Count", each [a=Table.TransformColumnTypes(_, {{"period", type text},{"values", type number}}),b=Table.Pivot(a, List.Distinct(a[period]), "period", "values", List.Sum)][b]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"01.09.2024", "01.10.2024"}, {"01.09.2024", "01.10.2024"})
in
#"Expanded Count"
Just load the table, select merge, and for the second table, just select the first table again, select full outer join. Done.
--Nate
I get this
from this
OK I understand what you mean now. Make sure your table is sorted on ID AND THEN DATE, add an index column, starting with one, and then an index column, starting with zero, and then use the merge GUI to join the table to itself by selecting the current table as your right table, join on the zero index in the first column, and the one index in the second column.
--Nate
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
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?
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.
if "left" is your original table (with 01.09 and 01.10) then why result of your "left outer join" has only data with 01.10?
Hi @yevhen_87
In Power Query within Power BI, you can perform a self-join to create a table like the one on the right. Here’s how you can do it step-by-step:
Load the data into Power Query:
Duplicate the table:
Rename columns (optional for clarity):
Create a relationship between current and previous periods:
Merge the tables:
Expand the merged table:
Remove unnecessary columns:
Finalize:
Your resulting table should now contain period, id, current (from the original values column), and previous (the previous period's values). This structure will match the desired format.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Could this be done without duplicating (when dataset is to large) or even with duplicating is it a good practice to filter one table with current period and duplicated with previous and then merge them by id?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
12 | |
10 | |
9 | |
7 |
User | Count |
---|---|
40 | |
26 | |
16 | |
16 | |
11 |