Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've been trying to do calculate a running total that subtracts based on multiple conditions in power query, im thinking DAX might be more suitable.
The product might change but it needs the last column needs to subtract based on the conditions that it needs to be the same product/store/warehouse. I hope that makes sense.
Also the total available is just in this table to make it easier to understand, i know its not the best way to represent the data.
Some sample data for example:
Store | Product | warehouse number | QTY | TOTAL available at relevant warehouse | Subtract running total if available |
123 | APPLE | 1 | 5 | 10 | 5 |
123 | APPLE | 1 | 4 | 1 | |
123 | APPLE | 1 | 1 | 0 | |
123 | APPLE | 1 | 3 | 0 | |
789 | APPLE | 2 | 6 | 15 | 9 |
789 | APPLE | 2 | 6 | 3 | |
789 | APPLE | 2 | 6 | 0 | |
789 | APPLE | 2 | 6 | 0 | |
789 | APPLE | 2 | 6 | 0 |
Solved! Go to Solution.
assuming there are no duplicate rows, we can make use of the Table.PositionOf function and avoid the steps to introduce the auxiliary index column and then to eliminate it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgwI8HEF0oZAbAqiDcCMWB1sCkyAGMzELm0IkTbAIW2MLG1uYYkkbQTEZiBlICdY4lEANgavND7jiZKOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Product = _t, war = _t, QTY = _t, TOTAL = _t, CheckSub = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TOTAL", type number}, {"QTY", type number}}),
grp= Table.Group(#"Changed Type", "TOTAL", {"mmm", (r)=> Table.AddColumn(r,"sub", each List.Max({r[TOTAL]{0}-List.Accumulate({0..Table.PositionOf(r,_)},0,(s,c)=>s+r[QTY]{c}),0}))},GroupKind.Local, (x,y)=>Number.From( x=y or y<>null)),
#"Expanded mmm" = Table.ExpandTableColumn(grp, "mmm", {"Store", "Product", "war", "QTY", "CheckSub", "sub"}, {"Store", "Product", "war", "QTY", "CheckSub", "sub"})
in
#"Expanded mmm"
of course there are simpler and perhaps direct solutions, but just to use some particular functions that MS developers offer us, ...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgwI8HEF0oZAbAqiDcCMWB1sCkyAGMzELm0IkTbAIW2MLG1uYYkkbQTEZiBlICdY4lEANgavND7jiZKOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Product = _t, war = _t, QTY = _t, TOTAL = _t, CheckSub = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TOTAL", type number}, {"QTY", type number}}),
grp= Table.Group(#"Changed Type", "TOTAL", {"mmm", (r)=> Table.AddColumn(Table.AddIndexColumn(r,"idx",0,1),"sub", each List.Max({r[TOTAL]{0}-List.Accumulate({0.._[idx]},0,(s,c)=>s+r[QTY]{c}),0}))
},GroupKind.Local, (x,y)=>Number.From( x=y or y<>null)),
#"Expanded mmm" = Table.ExpandTableColumn(grp, "mmm", {"Store", "Product", "war", "QTY", "CheckSub", "idx", "sub"}, {"Store", "Product", "war", "QTY", "CheckSub", "idx", "sub"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded mmm",{"idx"})
in
#"Removed Columns"
this solution is based on the fact that the empty cells of the TOTAL column are set to null.
If this value is different, for example empty, you have to change the corresponding value in the expression in the table.group
assuming there are no duplicate rows, we can make use of the Table.PositionOf function and avoid the steps to introduce the auxiliary index column and then to eliminate it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgwI8HEF0oZAbAqiDcCMWB1sCkyAGMzELm0IkTbAIW2MLG1uYYkkbQTEZiBlICdY4lEANgavND7jiZKOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Product = _t, war = _t, QTY = _t, TOTAL = _t, CheckSub = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TOTAL", type number}, {"QTY", type number}}),
grp= Table.Group(#"Changed Type", "TOTAL", {"mmm", (r)=> Table.AddColumn(r,"sub", each List.Max({r[TOTAL]{0}-List.Accumulate({0..Table.PositionOf(r,_)},0,(s,c)=>s+r[QTY]{c}),0}))},GroupKind.Local, (x,y)=>Number.From( x=y or y<>null)),
#"Expanded mmm" = Table.ExpandTableColumn(grp, "mmm", {"Store", "Product", "war", "QTY", "CheckSub", "sub"}, {"Store", "Product", "war", "QTY", "CheckSub", "sub"})
in
#"Expanded mmm"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |