Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
pcda
Helper I
Helper I

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! 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

yingyinr_1-1663234154662.png

yingyinr_0-1663234053347.png

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

View solution in original post

4 REPLIES 4
speedramps
Community Champion
Community Champion

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 NUMBERBATCHEXPIRATION DATE
1KS1BN0930/09/2022
2MS1FF8329/02/2024
3LS1EX35,LS1EX3531/08/2023,31/08/2023
4LS1EY9131/08/2023
500
6LS1EY9331/08/2023
7LS1EY9131/08/2023
800
900
10LS1FA98,LS1FA9831/03/2024,31/03/2024
11LS1EW87,MS1FF83,MS1FF8329/02/2024,29/02/2024,30/04/2023
12KS1BL08,MS1FS9130/09/2022,31/05/2024
1300
14LS1EB8131/03/2023
1500
1608092009/09/2021
1700
18LS1EW87,MS1FF8329/02/2024,30/04/2023
191012207/01/2023
20KS1BR31,L51EW40,MS1FF8328/02/2024,30/04/2023,30/09/2022
Anonymous
Not applicable

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"

yingyinr_1-1663234154662.png

yingyinr_0-1663234053347.png

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!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.