Find the Batch sizes listing all batches for product in a day

[lokeswer]

Hey!

I have a dataset listed by Date/time that shows the production runs by-product throughout a day, the catch is the same product could be made in different batch sizes at different times of the day. 

I am trying to essentially find the largest Batch size & compare times M-O-M, but as a first step trying to calculate the batch sizes at different times of the day. 

I tried indexing & ranking but got stuck on how to differentiate the same Product "Batch" as the product name looks identical with only the time stamps being unique

wanted it to look more like 

Date	Product	Batch	Count
1/2/2023	X539-SA-84.8-0.197	A	4
1/2/2023	X539-SA-64.5-0.126	B	2
1/2/2023	X539-SA-77-0.135	C	2
1/2/2023	X528-SA-77-0.095	D	2
1/2/2023	X621-HS3-73.5-0.197	E	6
1/2/2023	X539-SA-84.8-0.197	A	4
1/2/2023	5182-DS-77-0.12	F	2

Not sure if there is a better way

any help or leads would be much appreciated!

ReceiveTimestamp	Custom
1/2/2023 0:05	X539-SA-84.8-0.197
1/2/2023 0:12	X539-SA-84.8-0.197
1/2/2023 0:47	X539-SA-84.8-0.197
1/2/2023 1:05	X539-SA-84.8-0.197
1/2/2023 1:21	X539-SA-64.5-0.126
1/2/2023 1:21	X539-SA-64.5-0.126
1/2/2023 1:22	X539-SA-77-0.135
1/2/2023 1:57	X539-SA-77-0.135
1/2/2023 2:05	X528-SA-77-0.095
1/2/2023 2:14	X528-SA-77-0.095
1/2/2023 2:46	X621-HS3-73.5-0.197
1/2/2023 2:51	X621-HS3-73.5-0.197
1/2/2023 2:56	X621-HS3-73.5-0.197
1/2/2023 3:02	X621-HS3-73.5-0.197
1/2/2023 3:03	X621-HS3-73.5-0.197
1/2/2023 3:08	X621-HS3-73.5-0.197
1/2/2023 3:21	X539-SA-84.8-0.197
1/2/2023 3:29	X539-SA-84.8-0.197
1/2/2023 3:39	X539-SA-84.8-0.197
1/2/2023 4:32	X539-SA-84.8-0.197
1/2/2023 4:38	5182-DS-77-0.12
1/2/2023 4:49	5182-DS-77-0.12

[jennratten]

Can you please explain what is considered to be a "batch"? In the table you provided with the requested outcome, how did you come up with that? 

[lokeswer]

Hey @jennratten! 

I considered a batch as a group of items that are made together. e.g like 15 blue shoes, 20 brown shoes,10 yellow, 7 blue(again) throughout the timeline. 

for your second question, that's how i envisioned the final data to be, was the question too as to how to group em like that with "batch size" 

[jennratten]

So is the sample data you provided all of the records for one batch and you have just omitted the specific columns that are used to identify a batch?

[lokeswer]

Hey Jenn!

currently the only way to create/label them as batches is looking at the products themselves in "Custom" column. if they are repeating we consider as a batch. There's nothing in data to actually says batch 1, 2, 3. Guess that;s what i was trying to find how to do

does that help clarify?

[jennratten]

@lokeswer Have you had a chance to review this latest reply? 

[spinfuzer]

I do not believe anyone understands your vision of how to get from your raw data to the final outcome.

Go through some specific Examples of Receive, Timestamp, and Custom and how that translates into your final outcome.

The custom and timestamp columns are disappearing, and it is hard to understand what happens to them and how they suddenly turn into letters and numbers.

[lokeswer]

Hey Spinfuzer!

Thanks for your reply. I am not sure I see what disappears.

Both tables are just examples of how data is. 

I consolidated the both together to see if it helps

Date	Product	Batch	Count
1/2/2023	X539-SA-84.8-0.197	A	4
1/2/2023	X539-SA-64.5-0.126	B	2
1/2/2023	X539-SA-77-0.135	C	2
1/2/2023	X528-SA-77-0.095	D	2
1/2/2023	X621-HS3-73.5-0.197	E	6
1/2/2023	X539-SA-84.8-0.197	A	4
1/2/2023	5182-DS-77-0.12	F	2

[spinfuzer · Accepted solution]

Do you really need to label Batch as A through F given that you already have Product?

Just do a normal group by with count and then afterwards adjust the formula and add GroupKind.Local to the end.  GroupKind.Local will only group CONSECUTIVE matching group by columns (unless you do a custom local comarison formula).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndI5DsJADAXQq6CpM8n4256tQ6KgT4MU5f7XICMWZQFh0T95+54mRwMGBPAp1KCuczfl4sezz9JnH3oqyc3dhhFMTJKBka0pVdCKRem1McR/2XqFlBpi3SNNPxFe4yO/USgHRGJAEhuKIH8d2Sd+zL6/BKqS0ZnqcQ0wOja6bHObqL4Fv7BiYmxhUtnyuwtrOyhl+Mv4jB57I+WTme8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReceiveTimestamp = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ReceiveTimestamp", type datetime}, {"Custom", type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Changed Type", {{"ReceiveTimestamp", each Text.BeforeDelimiter(Text.From(_, "en-US"), " "), type date}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"ReceiveTimestamp", "Custom"}, {{"Count", each Table.RowCount(_), Int64.Type}}, GroupKind.Local)
in
    #"Grouped Rows"

[lokeswer]

Worked Magic!

we didn't have to create a new batch to your point since the Product is there. I'll try applying for all data as well. Thanks much @spinfuzer