Merging tables within query

[yevhen_87]

In a table with columns like: period, id and value I need to merge values by id from a previous period with a recent period.

[Anonymous · Accepted solution]

Hi @yevhen_87 

Based on your description you can refetr to the following sample.

Sample data 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtSz1DMyMDJR0lEyMDAEkoYGSrE6aOJGQNIIJm5ogKrBGJsESIcJTMICVYOJKaY4SL2xmVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [period = _t, id = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period", type date}, {"id", Int64.Type}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=[id],
b=[period],
c=List.Max(Table.SelectRows(#"Changed Type",each [id]=a and [period]<b)[period])
in c),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"period", Order.Ascending}, {"id", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Custom", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"id", "Custom"}, #"Changed Type", {"id", "period"}, "Changed Type1", JoinKind.LeftOuter),
    #"Expanded Changed Type1" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type1", {"value"}, {"value.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Changed Type1", "New Period", each if [Custom]<>null then Text.From([Custom])&"-"&Text.From([period]) else Text.From([period])),
    #"Inserted Sum" = Table.AddColumn(#"Added Custom1", "Addition", each List.Sum({[value.1], [value]}), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Sum",{"Custom", "value.1"})
in
    #"Removed Columns"

 Ouptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yevhen_87]

Period values are in a format like 
1.9.2024
1.9.2024
1.10.2024
1.10.2024, etc.

[Omid_Motamedise]

What is the format of values in the period  column?

[yevhen_87]

My replies are not visible so I posted a comment to my post.