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.
Hi community,
This time, I need some help from you to count or assign a count each time some rows sum X quantity.
For example, in the next table I would like to know how to get the column "Pallet List 2"
My required conditionals are:
I would really appreciate if you can help me Community,
Thnks in advantage.
Solved! Go to Solution.
You can create a "conditional" running total using List.Generate; then use List.Accumulate to develop the Pallet List.
Data
let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", type text}, {"QTY", Int64.Type}}),
//generate list of pallets
//replace >=80 with nulls to satisfy that condition
qty= List.Buffer(List.Transform(#"Changed Type"[QTY], each if _ >=80 then null else _)),
//Generate a running total of the qtys that start over when there is a null
// (ie an entry that was >=80) or when the running total reaches a Max of 80
qtyList = List.Generate(
()=>[qt= qty{0}, idx=0],
each [idx] < List.Count(qty),
each [qt= if qty{[idx]+1} = null then null
else if [qt] = null then qty{[idx]+1}
else if [qt] + qty{[idx]+1} <=80 then [qt] + qty{[idx]+1}
else qty{[idx]+1},
idx = [idx]+1],
each [qt]),
//generate the Pallet list by incrementing the list whenever the qty entry = the entry in the qtyList
//Note that the seed has to be {0} as {} or {null} + any number => null
//So we make the seed a {0} and remove that first entry when done generating the list.
palletList = List.RemoveFirstN(
List.Accumulate({0..List.Count(qty)-1},{0},(state,current)=>
if qtyList{current} = null then state & {null}
else if qtyList{current} = qty{current} then state & {List.Max(state)+1}
else state & {List.Max(state)}
),1),
//add to table
tbl = Table.FromColumns(
Table.ToColumns(#"Changed Type") & {palletList},
Table.ColumnNames(#"Changed Type") & {"Pallet List"})
in
tbl
Results
@jmontes1810 can you try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndJLDsIwDATQq6Csi2R7/InPUvX+14BSqKipQCFLa54msTLPDenamRXRpnZ5O0xlsE3bMj2QwQKcY0h7EHMfRev1/moaRC7umlYy8qspBR+ZcyQvJARY+hjamrhk8B3B7vszLRk/RTg2ScnEKdIdBSi5vqmv6Lpnkjzs+NWYnpHlBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DN = _t, QTY = _t, #"Pallet List 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", Int64.Type}, {"QTY", Int64.Type}, {"Pallet List 2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Pallet List 2"}),
Value = #"Removed Columns"[QTY],
Loop = List.Generate(
()=>[i=0,k=if Value{i}>=80 then 0 else Value{i}],
each [i]<List.Count(Value),
each [i=[i]+1, k=if Value{i}>=80 then 0 else Value{i}+[k]],
each [k]
),
Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type")&{Loop},List.Combine({Table.ColumnNames(#"Changed Type"),{"Helper"}})),
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each if [QTY]<80 then Number.IntegerDivide([Helper],80)+1 else null)
in
#"Added Custom"
Im about to try it...
Just to confirm, in my new table, in the advanced editor I just need to paste the code youre providen me but replacing each "Table.XXX" for the name of my table uploaded table. Right?
@jmontes1810 if you want to try out PQ, keep things unchanged from
#"Removed Columns"
I think the PQ form works, but, when I use the code, the data just retrive me the data of this example table. Now, what about if i want to use another Sheet/data? What should I do or change? My data wil be updated daily
@jmontes1810 load your data in PQ and that would result in Source followed by #"Changed Type". Once you have that and as long as the structure of the table is similar to the sample, you don't need to change anything till the end except pasting it in the query editor. If you prefer you can adapt the dax code as well.
Hi @smpa01
It almost works, but look... Are some count where the column sum over than 80, like the 5 and 6, in these two are 90 pcs.
I attach the data bellow...
DN | QTY | Helper | Custom |
3961178426 | 10 | 10 | 1 |
3961210671 | 10 | 20 | 1 |
3961254067 | 10 | 30 | 1 |
3961275157 | 10 | 40 | 1 |
3961377408 | 10 | 50 | 1 |
3961578272 | 10 | 60 | 1 |
3962085691 | 10 | 70 | 1 |
3962464346 | 10 | 80 | 2 |
3964382459 | 10 | 90 | 2 |
3964382465 | 10 | 100 | 2 |
3964681873 | 10 | 110 | 2 |
3964733826 | 10 | 120 | 2 |
3964787919 | 10 | 130 | 2 |
3964907400 | 10 | 140 | 2 |
3964914982 | 10 | 150 | 2 |
3966179015 | 10 | 160 | 3 |
3966243202 | 10 | 170 | 3 |
3961079096 | 20 | 190 | 3 |
3961079104 | 20 | 210 | 3 |
3961322655 | 20 | 230 | 3 |
3964714777 | 20 | 250 | 4 |
3964892006 | 20 | 270 | 4 |
3966154221 | 20 | 290 | 4 |
3964580786 | 30 | 320 | 5 |
3966243208 | 30 | 350 | 5 |
3966246800 | 30 | 380 | 5 |
3964580778 | 40 | 420 | 6 |
3964056235 | 50 | 470 | 6 |
3964712764 | 50 | 520 | 7 |
3964712772 | 50 | 570 | 8 |
3964825696 | 80 | 0 | |
3965997185 | 680 | 0 |
@jmontes1810 can you please show what is your desired output for the dataset you provided?
@smpa01
This is what we actually get...
This is the desired output
DN | QTY | Helper | Desired Count |
3961178426 | 10 | 10 | 1 |
3961210671 | 10 | 20 | 1 |
3961254067 | 10 | 30 | 1 |
3961275157 | 10 | 40 | 1 |
3961377408 | 10 | 50 | 1 |
3961578272 | 10 | 60 | 1 |
3962085691 | 10 | 70 | 1 |
3962464346 | 10 | 80 | 2 |
3964382459 | 10 | 90 | 2 |
3964382465 | 10 | 100 | 2 |
3964681873 | 10 | 110 | 2 |
3964733826 | 10 | 120 | 2 |
3964787919 | 10 | 130 | 2 |
3964907400 | 10 | 140 | 2 |
3964914982 | 10 | 150 | 2 |
3966179015 | 10 | 160 | 3 |
3966243202 | 10 | 170 | 3 |
3961079096 | 20 | 190 | 3 |
3961079104 | 20 | 210 | 3 |
3961322655 | 20 | 230 | 3 |
3964714777 | 20 | 250 | 4 |
3964892006 | 20 | 270 | 4 |
3966154221 | 20 | 290 | 4 |
3964580786 | 30 | 320 | 5 |
3966243208 | 30 | 350 | 5 |
3966246800 | 30 | 380 | 6 |
3964580778 | 40 | 420 | 6 |
3964056235 | 50 | 470 | 7 |
3964712764 | 50 | 520 | 8 |
3964712772 | 50 | 570 | 9 |
3964825696 | 80 | 0 | |
3965997185 | 680 | 0 |
You can create a "conditional" running total using List.Generate; then use List.Accumulate to develop the Pallet List.
Data
let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DN", type text}, {"QTY", Int64.Type}}),
//generate list of pallets
//replace >=80 with nulls to satisfy that condition
qty= List.Buffer(List.Transform(#"Changed Type"[QTY], each if _ >=80 then null else _)),
//Generate a running total of the qtys that start over when there is a null
// (ie an entry that was >=80) or when the running total reaches a Max of 80
qtyList = List.Generate(
()=>[qt= qty{0}, idx=0],
each [idx] < List.Count(qty),
each [qt= if qty{[idx]+1} = null then null
else if [qt] = null then qty{[idx]+1}
else if [qt] + qty{[idx]+1} <=80 then [qt] + qty{[idx]+1}
else qty{[idx]+1},
idx = [idx]+1],
each [qt]),
//generate the Pallet list by incrementing the list whenever the qty entry = the entry in the qtyList
//Note that the seed has to be {0} as {} or {null} + any number => null
//So we make the seed a {0} and remove that first entry when done generating the list.
palletList = List.RemoveFirstN(
List.Accumulate({0..List.Count(qty)-1},{0},(state,current)=>
if qtyList{current} = null then state & {null}
else if qtyList{current} = qty{current} then state & {List.Max(state)+1}
else state & {List.Max(state)}
),1),
//add to table
tbl = Table.FromColumns(
Table.ToColumns(#"Changed Type") & {palletList},
Table.ColumnNames(#"Changed Type") & {"Pallet List"})
in
tbl
Results
@jmontes1810 will respond back to you
@jmontes1810 sorry I can't seem to resolve this
@ronrsnfld are you able to take a look please ?
I posted a possible solution using List.Generate and List.Accumulate. Thanks for the ping.
@jmontes1810 in DAX
Column =
VAR _0 =
CALCULATE ( MAX ( tbl[Index] ) )
VAR _1 =
IF (
tbl[QTY] >= 80,
0,
CALCULATE ( SUM ( tbl[QTY] ), FILTER ( ALL ( tbl ), tbl[Index] <= _0 ) )
)
VAR _2 =
IF ( tbl[QTY] < 80, QUOTIENT ( _1, 80 ) + 1, BLANK () )
RETURN
_2
You need to have an index column in the data in order for DAX to work
@jmontes1810 Can you post sample data as text? You will need an Index column to define "before". Then something Cthulhu: Cthulhu - Microsoft Power BI Community
Thnks for your promp reply @Greg_Deckler
Here are the sample data in text form, hoping it can help.
As my mortal view, I think Cthulhu cant works for what im seeking 'cause it count the times some same value/text appears in consecutive form due to an index order.
My index order in this case is to sort from smaller to biger quantity. But, what about the sum conditional? To count each time the rows sum 80 and only for qtys less than 80. I hope you can elp me.
DN | QTY | Pallet List 2 |
3964811437 | 10 | 1 |
3965357319 | 10 | 1 |
3964870118 | 10 | 1 |
3964811439 | 10 | 1 |
3964870119 | 10 | 1 |
3966266495 | 20 | 1 |
3964892320 | 20 | 2 |
3962033596 | 20 | 2 |
3964892321 | 30 | 2 |
3963548154 | 60 | 3 |
3964892322 | 70 | 4 |
3964730916 | 80 | - |
3964906757 | 100 | - |
@jmontes1810 Not sure QTY sort is going to work but will have to try some things.
@jmontes1810 If you add an index, try:
Column =
VAR __Limit = 80
VAR __Value = SUMX(FILTER(ALL('Table8'),[Index]<=EARLIER('Table8'[Index])),[QTY])
RETURN
TRUNC(DIVIDE(__Value,__Limit))+1
Hi @Greg
It almost works, but look... Are some count where the column sum over than 80, like the 5 and 6, in these two are 90 pcs.
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |