Joins within a query

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

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

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