Forum Discussion
SOLVED: Count rows based on column values greater than record value (different column)
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDIZfZfQ8aPKndTM3H0DYvfTgTS8ibu+Pdd26zoGHkPCRfElCMGxaw5YtCNwwlJwSNZfrjGXBlFjB4/3xnlKmErENBvVAr76rNKdM8UVDreAlskJSBetyc6eQosCqYFahg2Z/iZsH/PbRthe2X+3VQ6/bcxoP1/ifvSSV5vxHg6KK8QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, #"Order date" = _t, #"Shipping date" = _t, #"Order type" = _t, #"ongoing orders" = _t, #"ongoing shirt order" = _t, #"ongoind pants order" = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Order ID", Int64.Type}, {"Order date", type datetime}, {"Shipping date", type datetime}, {"Order type", type text}, {"ongoing orders", Int64.Type}, {"ongoing shirt order", Int64.Type}, {"ongoind pants order", Int64.Type}},"en-US"), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "oo", each Table.RowCount(Table.SelectRows(#"Modificato tipo", (r)=>r[Shipping date]>[Order date] and r[Order date]<[Order date] ))), #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "oos", each Table.RowCount(Table.SelectRows(#"Modificato tipo", (r)=>r[Shipping date]>[Order date] and r[Order date]<[Order date] and r[Order type]="shirt" ))), #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "oop", each Table.RowCount(Table.SelectRows(#"Modificato tipo", (r)=>r[Shipping date]>[Order date] and r[Order date]<[Order date] and r[Order type]="pants" ))) in #"Aggiunta colonna personalizzata2"
Thanks a million for the speedy response, clarifications:
1) the date format is M/D/Y
2) on a specific row, the "order date" needs to be verified against all other record's "shipping date" and i need a count of record where "shipping date" is > "order date". I don't know if i'm being clear 🙂
3) The count needs to be on the whole table.
My table has about 40k record per day and i will need to keep the data to report trend for 13months. currently i have 43 days of record and the excel file is starting to slow down (8mn to open, 3mn to calulate) i only show a few columns here, but there are 23 columns in the set.
Thanks again for the help !
2) on a specific row, the "order date" needs to be verified against all other record's "shipping date" and i need a count of record where "shipping date" is > "order date". I don't know if i'm being clear
I don't understand how, with this rule, the values of the [Ongoing Order] column are obtained.
Could you give a more specific example of this calculation, in the case, for example, of the 2 of line 5?
Hello Anonymous ,
For line 2: The "order date" is : 1/3/2021 8:57:00 AM, i need to check all other orders where the shipping date / time is greater than this , the only one that fits is order 1, "shippping date" is 1/3/2021 10:00:00 AM therefore the count is 1
For line 5: The "order date" is : 2/6/2021 11:03:00 PM, if you check all other order where the shipping date is greater, the order that fits are: 3 & 4 as their respective "shipping date" are 2/6/2021 11:30:00 PM and 2/8/2021 7:00:00 AM which are both greater than the record order date so the count is 2. Order 3 is shirt and order 4 is pants so the numbers get into the right columns as well 🙂
Thanks !
Wait, i believe I understand what does not make sense !
The count, will count all of the ongoing orders which actually means, the counted record will have an "order date" < current record "order date" and a "shipping date" > current record "Order date"...
A "small" thing i completely forgot to mention 🙂
I have been at this for too long ... 2 weeks trying to figure out this makes me feel a bit dumb and my brain is starting to become mush ^^