2 ACCEPTED SOLUTIONS
Hi @jhaanand81 ,
Based on your description, you can try this query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZdJduQwCEDvknVKj3k4S17uf42m2rJLsul17/IgX4wG6ufni/GF8CIg/Pr+Infmofj1+33TvIwAE2BgPJWGxQUOkkMHL8gLdBeDYfpXRbyo0nASsJlCE3Ad7AciC1IqIvqfDLioj+yYekzbeMgNdaQ1zDvjcDCyMizCMOZz5AtSmpnMnUBlRRqBT6RUpp0VNNbEnjGumjZMUiAMsCdSmuSGoAzGHNJYKZWGdZ6FqOCAaDwLcWgZEy3XsmOsktZFUwXQtKuefqsNYhcPWooO5CYedORs7QhljqTOjkhrJwJABjeZLk10mUZSYxneeFYqF++i4ar04KbTSmPTL906ICThKmesHVDVzIaoDw3jCn4lSnNGshGVRwwd0CDvFJ+duTMoXDl2bxgUnW/dYkmRGmrSxJJKRyxIu5X082Nanyp5qDeApGSTRkmdKby9L6DUuFpyk2gdYplNcnOIlTuHgqDLXxDONkS7DRaq6s1PEWI1ocyqHcOMaAOkYZiJO6YSkjqsMSNZbXIQq/jFSeky4sgWrkOXs8wcScHco6EaOqOpbGmEuUO4/rKzF3emUiOzeWCvYgT6gJlQ3goZORvoxmjVSwZqw6jqOW/+3b9bdj79uxNoNSbz3NUbUio7J+7OAGnWkMCGqdREa4cjhM6Pfmc40rxjxPA9vzpGjD57arNToy2GPxNaGnZrEXCj66vfGQjPhqlv/j3C7ImUJjortYzks1tXojQx/b31ZpDSmIuK9uYMPo3vjNfE/0zjjXGTcxnujNRr187bkGqNzjMmlxhzLG1Eac4Qt+ETgNKcii82UK7tmfJUsnpdSjSEnzp6J+2kNlNZV1fTtDV/znPk5lltwhnjOn+qu/JzBmzzyqiTvw/iTl43CDQdtHyPq7huQ+oer/UXjdypxlQjrxP8s8LXd67b4uZkLf3GGynptXLXtkAkbuRUvxekkQdf42qVL+feLl9yvw+KahZt5LXbuPn/CJ8zamtfqrPI4/xtspst8e8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Date" = _t, #"Amount in doc. curr." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Date", type text}, {"Amount in doc. curr.", type number}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Document Date", type date}}, "en-GB"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [#"Amount in doc. curr."] > 1),
#"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Amount in doc. curr."}, {"Added Index1.Amount in doc. curr."}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Added Index1.Amount in doc. curr.", "Output1"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [#"Amount in doc. curr."] < 0),
#"Added Index2" = Table.AddIndexColumn(#"Filtered Rows1", "Index.1", 1, 1, Int64.Type),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Index"}, #"Added Index2", {"Index.1"}, "Added Index2", JoinKind.LeftOuter),
#"Expanded Added Index2" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index2", {"Amount in doc. curr."}, {"Added Index2.Amount in doc. curr."}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Added Index2",{{"Added Index2.Amount in doc. curr.", "Ouput2"}}),
BufferedValues = List.Buffer(#"Renamed Columns1"[Output1]),
CumulativeTotal = Table.AddColumn(#"Renamed Columns1", "Output3", each -16197615.03 + List.Sum(List.FirstN(BufferedValues,[Index])),type number),
#"Removed Columns" = Table.RemoveColumns(CumulativeTotal,{"Index"})
in
#"Removed Columns"
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jhaanand81 ,
Based on your description, you can try this query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZdJduQwCEDvknVKj3k4S17uf42m2rJLsul17/IgX4wG6ufni/GF8CIg/Pr+Infmofj1+33TvIwAE2BgPJWGxQUOkkMHL8gLdBeDYfpXRbyo0nASsJlCE3Ad7AciC1IqIvqfDLioj+yYekzbeMgNdaQ1zDvjcDCyMizCMOZz5AtSmpnMnUBlRRqBT6RUpp0VNNbEnjGumjZMUiAMsCdSmuSGoAzGHNJYKZWGdZ6FqOCAaDwLcWgZEy3XsmOsktZFUwXQtKuefqsNYhcPWooO5CYedORs7QhljqTOjkhrJwJABjeZLk10mUZSYxneeFYqF++i4ar04KbTSmPTL906ICThKmesHVDVzIaoDw3jCn4lSnNGshGVRwwd0CDvFJ+duTMoXDl2bxgUnW/dYkmRGmrSxJJKRyxIu5X082Nanyp5qDeApGSTRkmdKby9L6DUuFpyk2gdYplNcnOIlTuHgqDLXxDONkS7DRaq6s1PEWI1ocyqHcOMaAOkYZiJO6YSkjqsMSNZbXIQq/jFSeky4sgWrkOXs8wcScHco6EaOqOpbGmEuUO4/rKzF3emUiOzeWCvYgT6gJlQ3goZORvoxmjVSwZqw6jqOW/+3b9bdj79uxNoNSbz3NUbUio7J+7OAGnWkMCGqdREa4cjhM6Pfmc40rxjxPA9vzpGjD57arNToy2GPxNaGnZrEXCj66vfGQjPhqlv/j3C7ImUJjortYzks1tXojQx/b31ZpDSmIuK9uYMPo3vjNfE/0zjjXGTcxnujNRr187bkGqNzjMmlxhzLG1Eac4Qt+ETgNKcii82UK7tmfJUsnpdSjSEnzp6J+2kNlNZV1fTtDV/znPk5lltwhnjOn+qu/JzBmzzyqiTvw/iTl43CDQdtHyPq7huQ+oer/UXjdypxlQjrxP8s8LXd67b4uZkLf3GGynptXLXtkAkbuRUvxekkQdf42qVL+feLl9yvw+KahZt5LXbuPn/CJ8zamtfqrPI4/xtspst8e8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Date" = _t, #"Amount in doc. curr." = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Document Date", type text}, {"Amount in doc. curr.", type number}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Document Date", type date}}, "en-GB"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type with Locale", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [#"Amount in doc. curr."] > 1),
#"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Amount in doc. curr."}, {"Added Index1.Amount in doc. curr."}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Added Index1.Amount in doc. curr.", "Output1"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [#"Amount in doc. curr."] < 0),
#"Added Index2" = Table.AddIndexColumn(#"Filtered Rows1", "Index.1", 1, 1, Int64.Type),
#"Merged Queries1" = Table.NestedJoin(#"Renamed Columns", {"Index"}, #"Added Index2", {"Index.1"}, "Added Index2", JoinKind.LeftOuter),
#"Expanded Added Index2" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index2", {"Amount in doc. curr."}, {"Added Index2.Amount in doc. curr."}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Added Index2",{{"Added Index2.Amount in doc. curr.", "Ouput2"}}),
BufferedValues = List.Buffer(#"Renamed Columns1"[Output1]),
CumulativeTotal = Table.AddColumn(#"Renamed Columns1", "Output3", each -16197615.03 + List.Sum(List.FirstN(BufferedValues,[Index])),type number),
#"Removed Columns" = Table.RemoveColumns(CumulativeTotal,{"Index"})
in
#"Removed Columns"
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
