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