Forum Discussion

yevhen_87's avatar
yevhen_87
Frequent Visitor
1 year ago
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?
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

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's avatar
    yevhen_87
    Frequent Visitor
    1 year ago

    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?