Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
yevhen_87
Frequent Visitor

Joins within a query

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

1 ACCEPTED SOLUTION
v-bofeng-msft
Community Support
Community Support

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

vbofengmsft_0-1731290288926.png

 

Best Regards,

Bof

 

View solution in original post

12 REPLIES 12
v-bofeng-msft
Community Support
Community Support

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

vbofengmsft_0-1731290288926.png

 

Best Regards,

Bof

 

Omid_Motamedise
Memorable Member
Memorable Member

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
Super User
Super User

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

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

I get this
2.png
from this
1.png

 

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
AlienSx
Super User
Super User

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? 

Poojara_D12
Solution Sage
Solution Sage

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

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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.