Forum Discussion
Future inventory level
Hi, carmae
Yes, if you want to group and accumulate data, you only need to go one more step to group by Item.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdIxDsMgDIXhuzCnAmxCyBF6hihjx269v8ryiBKePaJPWGD9xxHev883hyWkFiVHSVL6IadwLncT2Esn02FlsgJbJ1rHNZmsOiM35ynNubcPq0/TZL9Fx1rksZY9pmzd6yaOqfWHbtbKOl0rm606IzfnKc2Zea0sDxOnFhirBcZqgZGvg1gtMFYLjNUCY7XAWC3i1AJjtYhTC4zVAmO1wKyVGbXAWC0wVguM1QK71XL+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Date Item" = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date Item", type date}, {"Quantity", Int64.Type}}), FX = (values as list) as list => let GRTList = List.Generate( () => [GRT = values{0}, i = 0], each [i] < List.Count(values), each let nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1} in [GRT = nextGRT, i = [i] + 1], each [GRT] ) in GRTList, // Group by Item and apply transformations Grouped = Table.Group( #"Changed Type", {"Item"}, { {"Transformed", each let dateitemlist = List.Buffer([Date Item]), quantitylist = List.Buffer([#"Quantity"]), result = Table.FromColumns( {dateitemlist, quantitylist, List.Transform(FX(quantitylist), each if _ < 0 then 0 else _)}, {"Date Item", "Quantity", "Output"} ) in result } } ), #"Expanded Transformed" = Table.ExpandTableColumn(Grouped, "Transformed", {"Date Item", "Quantity", "Output"}, {"Date Item", "Quantity", "Output"}) in #"Expanded Transformed"Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, carmae
Thanks for Irwan reply. You can try the following M language in Power Query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/LCcAwDAPQXXxOiT/5zlKy/xoNtFEg9fUhI+u+iVtUicqaKJAwjfCaLrsMZrAES8syKCOmsOKcVqeiObkOK8uM/x2GGfrN6JHlzE1Tx+z8Zdo5bdKetq04p9WpaM7tniY0xgM=", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Item" = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Date Item", Int64.Type}, {"Quantity", Int64.Type}}
),
FX = (values as list) as list =>
let
GRTList = List.Generate(
() => [GRT = values{0}, i = 0],
each [i] < List.Count(values),
each
let
nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1}
in
[GRT = nextGRT, i = [i] + 1],
each [GRT]
)
in
GRTList,
dateitemlist = List.Buffer(#"Changed Type"[Date Item]),
quantitylist = List.Buffer(#"Changed Type"[Quantity]),
result = Table.FromColumns(
{Source[Date Item], Source[Quantity], List.Transform(FX(quantitylist), each if _ < 0 then 0 else _)},
{"Date Item", "Quantity", "Cumulative value"}
),
#"Changed Type1" = Table.TransformColumnTypes(result,{{"Date Item", type date}, {"Quantity", Int64.Type}, {"Cumulative value", Int64.Type}})
in
#"Changed Type1"
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Here's the table with the additional column to manage more than a single Item.
As said your solution works like a charm, I just need now to make it works with multiple items.
I suppose I should use something like Group rows, right?
Thanks again.
ItemDate ItemQuantity
Item1 08/21/2024 10 Item1 08/22/2024 -3 Item1 08/23/2024 -4 Item1 08/24/2024 5 Item1 08/25/2024 -2 Item1 08/26/2024 -4 Item1 08/27/2024 -3 Item1 08/28/2024 -4 Item1 08/29/2024 -6 Item1 08/30/2024 -2 Item1 08/31/2024 20 Item1 09/01/2024 -2 Item1 09/02/2024 -2 Item1 09/03/2024 -3 Item1 09/04/2024 5 Item1 09/05/2024 -5 Item1 09/06/2024 -3 Item1 09/07/2024 -2 Item1 09/08/2024 -5 Item1 09/09/2024 -1 Item2 08/21/2024 10 Item2 08/22/2024 -3 Item2 08/23/2024 -4 Item2 08/24/2024 5 Item2 08/25/2024 -2 Item2 08/26/2024 -4 Item2 08/27/2024 -3 Item2 08/28/2024 -4 Item2 08/29/2024 -6 Item2 08/30/2024 -2 Item2 08/31/2024 20 Item2 09/01/2024 -2 Item2 09/02/2024 -2 Item2 09/03/2024 -3 Item2 09/04/2024 5 Item2 09/05/2024 -5 Item2 09/06/2024 -3 Item2 09/07/2024 -2 Item2 09/08/2024 -5 Item2 09/09/2024 -1 Hi, carmae
Yes, if you want to group and accumulate data, you only need to go one more step to group by Item.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdIxDsMgDIXhuzCnAmxCyBF6hihjx269v8ryiBKePaJPWGD9xxHev883hyWkFiVHSVL6IadwLncT2Esn02FlsgJbJ1rHNZmsOiM35ynNubcPq0/TZL9Fx1rksZY9pmzd6yaOqfWHbtbKOl0rm606IzfnKc2Zea0sDxOnFhirBcZqgZGvg1gtMFYLjNUCY7XAWC3i1AJjtYhTC4zVAmO1wKyVGbXAWC0wVguM1QK71XL+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Date Item" = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date Item", type date}, {"Quantity", Int64.Type}}), FX = (values as list) as list => let GRTList = List.Generate( () => [GRT = values{0}, i = 0], each [i] < List.Count(values), each let nextGRT = if [GRT] > 0 then [GRT] + values{[i] + 1} else values{[i] + 1} in [GRT = nextGRT, i = [i] + 1], each [GRT] ) in GRTList, // Group by Item and apply transformations Grouped = Table.Group( #"Changed Type", {"Item"}, { {"Transformed", each let dateitemlist = List.Buffer([Date Item]), quantitylist = List.Buffer([#"Quantity"]), result = Table.FromColumns( {dateitemlist, quantitylist, List.Transform(FX(quantitylist), each if _ < 0 then 0 else _)}, {"Date Item", "Quantity", "Output"} ) in result } } ), #"Expanded Transformed" = Table.ExpandTableColumn(Grouped, "Transformed", {"Date Item", "Quantity", "Output"}, {"Date Item", "Quantity", "Output"}) in #"Expanded Transformed"Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Working great, thank you veeeeeery much!!
