Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Thanks for you help. However, the query are too complicated for me. I will try other way. Thank you.
Here's a sample query showing one way to do it with dummy data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8glXMDBU0lEKiQxwhbCMDIyMdQ0MdcEcQ6VYHdyKjCCKjLAoMkI3yQSvIqhJFnBFRnjcZIZXFdQoYyMsqjBcZWaCVxXULEMjoMNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LW = _t, TYPE = _t, DATE1 = _t, #"Run QTY" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LW", type text}, {"TYPE", type text}, {"DATE1", type date}, {"Run QTY", type number}}),
#"Self Merge on LW & TYPE" = Table.NestedJoin(#"Changed Type", {"LW", "TYPE"}, #"Changed Type", {"LW", "TYPE"}, "Self Merge", JoinKind.LeftOuter),
#"Filter Self Merge Dates" = Table.ReplaceValue(#"Self Merge on LW & TYPE", each [Self Merge], each let CurrentDate = [DATE1] in Table.SelectRows([Self Merge], each [DATE1] <= CurrentDate ), Replacer.ReplaceValue, {"Self Merge"}),
#"Aggregate Self Merge" = Table.AggregateTableColumn(#"Filter Self Merge Dates", "Self Merge", {{"Run QTY", List.Sum, "**bleep** Run Plan", type number}})
in
#"Aggregate Self Merge"
The query joins the table with itself on LW & TYPE, then filters the joined table, and sums the Run QTY.
Here's a variation that uses Table.AddColumn rather than using Table.ReplaceValue when filtering the joined table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8glXMDBU0lEKiQxwhbCMDIyMdQ0MdcEcQ6VYHdyKjCCKjLAoMkI3yQSvIqhJFnBFRnjcZIZXFdQoYyMsqjBcZWaCVxXULEMjoMNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LW = _t, TYPE = _t, DATE1 = _t, #"Run QTY" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LW", type text}, {"TYPE", type text}, {"DATE1", type date}, {"Run QTY", type number}}),
#"Self Merge on LW & TYPE" = Table.NestedJoin(#"Changed Type", {"LW", "TYPE"}, #"Changed Type", {"LW", "TYPE"}, "Self Merge", JoinKind.LeftOuter),
#"Added Self Merge Filtered" = Table.AddColumn(#"Self Merge on LW & TYPE", "Self Merge Filtered", each let CurrentDate = [DATE1] in Table.SelectRows([Self Merge], each [DATE1] <= CurrentDate )),
#"Remove Self Merge" = Table.RemoveColumns(#"Added Self Merge Filtered",{"Self Merge"}),
#"Aggregate Self Merge Filtered" = Table.AggregateTableColumn(#"Remove Self Merge", "Self Merge Filtered", {{"Run QTY", List.Sum, "**bleep** Run Plan", type number}})
in
#"Aggregate Self Merge Filtered"
Does something like this work at your end?
Regards