Forum Discussion
How do you extract an input from an array delimited by commas?
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!
Hi Anonymous ,
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
4 Replies
speedrampsSuper User
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.
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 Hi Anonymous ,
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