Joins within a query

[yevhen_87]

Is it possible to left join table on the left with itself to make it look like a table on the right?

[v-bofeng-msft · Accepted 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

[Omid_Motamedise]

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"

[watkinnc]

Just load the table, select merge, and for the second table, just select the first table again, select full outer join. Done.

--Nate

[yevhen_87]

I get this

from this

[watkinnc]

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

[AlienSx]

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]

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]

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]

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.

[AlienSx]

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? 

[Poojara_D12]

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:

1. Load the data into Power Query:

   • Start by loading your data table into Power Query.
   • Assume your table is named Data with columns period, id, and values.

2. Duplicate the table:

   • Right-click on the Data query in the Queries pane, and select Duplicate.
   • Rename the duplicated table to PreviousData.

3. Rename columns (optional for clarity):

   • In Data, rename values to current.
   • In PreviousData, rename values to previous.

4. Create a relationship between current and previous periods:

   • In the PreviousData table, go to the Add Column tab and select Custom Column.
   • Create a new column named Next Period with the following formula:
     PowerQuery:

     Date.AddMonths([period], 1)
     This assumes that each "previous" period is one month earlier than the current.

5. Merge the tables:

   • Go back to the Data query.
   • In the Home tab, select Merge Queries.
   • Choose PreviousData as the table to merge with.
   • In the Merge window, select id and period from Data, and select id and Next Period from PreviousData.
   • Choose a Left Outer Join to keep all rows from Data.

6. Expand the merged table:

   • After the merge, you will see a new column containing the PreviousData table.
   • Click the expand icon next to this column, and select the previous column to bring it into your main Data table.
   • This will add the previous column to your table.

7. Remove unnecessary columns:

   • If needed, remove any columns you don’t need (like Next Period or any duplicate columns).

8. Finalize:

   • Click Close & Apply to load the transformed data into Power BI.

Result

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

[yevhen_87]

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?