Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I doubt that this one has a solution in Power Query, however I thought to share it on this forum, maybe!
I have a dataset of weekly orders, which I need to limit to a weekly total quantity of 100. Thus remainder should be carried over to the following week and so on, till the last week. If there is remainder you open a new week. Always respecting the upper limit per week of 100.
Dataset for original table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/NCcAwCIDRXTwH6k9MhwnZf42mxKJgCqUHL48P0d4B24EEBXgOIYwySZ3UKFSSK87VZpdVJIskEDvVXNErMS6qfmqkp7K7NFSnU/tUbXbR3+p+e1w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Week = _t, Orders = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Week", Int64.Type}, {"Orders", Int64.Type}})
in
#"Changed Type"
here some logic of the "singular" mechanism that you have described :
let
order={140,80,60,220},
Source = List.Accumulate({1..9},[ord={List.Min({order{0},100})},res={List.Max({order{0}-100,0})}], (s,c)=> s&[ord=s[ord]&{List.Min({(order{c}? ?? 0)+s[res]{c-1},100})},res=s[res]&{List.Max({(order{c}? ?? 0)+s[res]{c-1}-100,0})}]),
ord = Source[ord]
in
ord
which transform
this list
in to this list
warning:
Using the list.accumulate function is not suitable for large tables
could you please give the result table you are after?
Another important aspect: should be the date column be sorted, before eatablish wich row has to be splitted?
Hi @Anonymous
I'm looking for the outcome table as such.
For the date column, not necessarily sorted, as long as the order and structure of the row/date/order-ID is maintained.
Is not completely clear to me the role of order id and what you mean
with this sentence: "as long as the order and structure of the row/date/order-ID is maintained".
If you don'thave sorted date and don't specify wich row you should move from actual week to the nex week (ween you have a surplus, as for week 2, you have many possibilities to chose. Which one is the rigth one?)
I'm afraid that the structure will not be maintained: wich id shoul have the yellow record of week 3?
Could you post a table with IDs too?
@Anonymous ,
My thoughts too. Which order id's quantity should be carried over and which order id in the next week this will be added to? There are no order id's in the sample provided.
Proud to be a Super User!
Hi @Ramiroz ,
Please try this. The code below adds a row for current week's remainder which is then carried over as another row of data for the next week.
// Query1 (3)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/NCcAwCIDRXTwH6k9MhwnZf42mxKJgCqUHL48P0d4B24EEBXgOIYwySZ3UKFSSK87VZpdVJIskEDvVXNErMS6qfmqkp7K7NFSnU/tUbXbR3+p+e1w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Week = _t, Orders = _t]),
#"Grouped Rows" = Table.Group(Source, {"Week"}, {{"Total", each List.Sum(List.Transform([Orders], Number.From))}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Total", Int64.Type}}),
#"Inserted Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [Total] - 100, type number),
#"Added Custom" = Table.AddColumn(#"Inserted Subtraction", "Excess for Next Week", each - List.Sum(List.FirstN(#"Inserted Subtraction"[Subtraction],[Index])), Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Added Custom", "Remainder from Last Week", each try -#"Added Custom"[Excess for Next Week]{[Index]-2} otherwise null, Int64.Type),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Week", "Excess for Next Week", "Remainder from Last Week"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Week"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Note"}, {"Value", "Orders"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "Date", each if [Note] = "Excess for Next Week" then "zzzz" else null, type text ),
Custom1 = Table.Combine({Source, #"Added Custom2"}),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Orders", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Week", Order.Ascending}, {"Date", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","zzzz",null,Replacer.ReplaceValue,{"Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Date", "Note", "Week", "Orders"})
in
#"Reordered Columns"
Proud to be a Super User!
Hi @danextian
I tried the code, however I need:
Hi @Jimmy801,
Yes I need it per day, no aggregation. As I have an order ID for each line, and need to keep the same structure, just reduce or increase or add a new entry to adjust to be below the 100 per week limit.
Hello @Ramiroz
this is for sure really challenging. Need List.Generate to go through the table, check week and orders, hold old week and current total orders of week, create new lines when some conditions is true. For sure feasable but I can't invest too much time fore a solution 🙂
Maybe @ImkeF is faster in that 🙂
Sorry
Jimmy
@Ramiroz , try following M code pls
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc/NCcAwCIDRXTwH6k9MhwnZf42mxKJgCqUHL48P0d4B24EEBXgOIYwySZ3UKFSSK87VZpdVJIskEDvVXNErMS6qfmqkp7K7NFSnU/tUbXbR3+p+e1w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Week = _t, Orders = _t]),
#"Grouped Rows" = Table.Group(Source, {"Week"}, {{"Total", each List.Sum(List.Transform([Orders], Number.From))}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate(
{0..[Index]},
0,
(c, s) => Number.Mod(c + Number.Mod(#"Added Index"[Total]{s}, 100), 100)
)
),
#"Merged Queries" = Table.NestedJoin(Source, {"Week"}, #"Added Custom", {"Week"}, "Added Custom", JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Custom"}, {"Custom"})
in
#"Expanded Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.