Forum Discussion
How do you extract an input from an array delimited by commas?
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
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
Worked perfectly, thank you!