How do you extract an input from an array delimited by commas?

[pcda]

Hi everyone! I have a column that has a list of dates separated by commas, however there might be any number of dates in them so I have no way to do this manually. I'm looking to extract the earliest date in a new column so that I can add that to one of my visuals.  Is there any way to do this? Or any alternate way to do it? The idea is that I can see which inventory batch is closest to expiration. 

Thank you! 

[speedramps]

We want to help you but your description is too vaugue.

Is there just one row in the table or multiple row by product or by customer? 

If so do you want the earliest date on the table or earliest date per product or customer?

Please write it again clearly.

Provide example input data as table text (not a screen print) so we can import the data to build a soution for you.
Also provide the example desired output, with a clear description of the process flow.

Don't care not to share private data.

Take care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time and effort into writing clear problem descriptions.

Vaugue descriptions waste your time and ourtime.

Look foward to helping you when the above information is forthcoming.

[pcda]

Thanks speedramps! 

So I have a column with the store ID, each store (row) can have 0 to any number of batches in it. If there are no batches, then the Batch column will have a 0 in it, other wise, it will have each batch serial ID separated by commas. The batch name is a serial ID like "KS1BN09" that has no context whatsoever. So a cell in the batch column could look like "0", "KS1BN09" or "KS1BN09,MS1FF83" or "KS9857,JK38475,JD89203" depending on how many batches that store has. 

Finally there is an expiration date column, that matches the batch to the expiration date respectively. So if batch KS1BN09 expires on 11/06/22, that will be the value in the cell, but if there is more than one batch, then there will be more than one expiration date separated by commas just like in the sample table below (the actual database is more than a few hundred thousand entries long). 

I need to be able to know which stores have batches that expire in the next 3 months. For that, I would need to be able to separate the dates, check if they expire soon, and then return a matrix with the store, batch number and their expiration date of those that expire soon. 

Basically, I'm looking for a way to iterate an array and see if it meets certain conditions. 

STORE NUMBER	BATCH	EXPIRATION DATE
1	KS1BN09	30/09/2022
2	MS1FF83	29/02/2024
3	LS1EX35,LS1EX35	31/08/2023,31/08/2023
4	LS1EY91	31/08/2023
5	0	0
6	LS1EY93	31/08/2023
7	LS1EY91	31/08/2023
8	0	0
9	0	0
10	LS1FA98,LS1FA98	31/03/2024,31/03/2024
11	LS1EW87,MS1FF83,MS1FF83	29/02/2024,29/02/2024,30/04/2023
12	KS1BL08,MS1FS91	30/09/2022,31/05/2024
13	0	0
14	LS1EB81	31/03/2023
15	0	0
16	080920	09/09/2021
17	0	0
18	LS1EW87,MS1FF83	29/02/2024,30/04/2023
19	10122	07/01/2023
20	KS1BR31,L51EW40,MS1FF83	28/02/2024,30/04/2023,30/09/2022

[v-yiruan-msft · Accepted solution]

Hi @pcda ,

First, you can split the columns [BATCH] and [EXPIRATION DATE] in Power Query Editor and get the data as below screenshot:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVLBDsIwCP2XnkkKdHP0qIm7OD24g5rF//8NB8OtVeOhKYXHg/fSaQoUIJxGOlwwz1HCiDkyMocnTIHn1Hmkvpc0R5wjshYbK2pqGOl4Ty34rQwUURSUYAsN3zj+kanCWVFb0Y6+dis0fUO7fzxS8eTqRbh09vss4LczJJMFW7g0kI+6SQfuA/zyA4pQLWy2hYjd4AHFesdl7dVoG9oWQ1O99Nu2g1C5rLPXvpEaJ5jZUtlH0FLraqh8a6s1fQpRMwmJVRB2EWmr2TwVeU0EQzuTNliSyg9SKP/a8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"STORE NUMBERBATCHEXPIRATION" = _t, BATCH = _t, #"EXPIRATION DATE" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE NUMBERBATCHEXPIRATION", Int64.Type}, {"BATCH", type text}, {"EXPIRATION DATE", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"BATCH", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "BATCH"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"EXPIRATION DATE", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EXPIRATION DATE"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter1"),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates","0","",Replacer.ReplaceValue,{"EXPIRATION DATE"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"EXPIRATION DATE", type date}})
in
    #"Changed Type1"

Then you can create a calculated column as below to judge if the batch will expire in the next 3 months...

Will expire in the next 3 months? = IF('Table'[EXPIRATION DATE]<TODAY()+90,1,0)

Best Regards

[pcda]

Worked perfectly, thank you!