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

     

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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"