Extract unordered comma delimeted data into columns containing identical data in Power Query

[Anonymous]

Hi guys, 

I'm relatively new to Power BI and I have encountered an issue that I hope you can help me resolve. 

I have several columns which contains rows that could look like below. 

Row1: 401,402,403

Row2: 405,403,407,408

Row3: 403,405,404,410,406,402

Etc. 

My issue is that I want to create new columns that either gives the value "1" if e.g. 401 is present in the row or extracts the value 401 to a new column that only contains the values 401 or "Null". 

If I use the Extract or Split by delimiter function I will get a column containing the values 401, 405 and 403 in this example. 

My data is connected to Dynamics through an API so the columns should be able to update accordingly. 

I hope the above makes some sense. Thanks in advance guys.

[Anonymous · Accepted solution]

Add the following formula to a customer column. It splits the line into a list based on a comma. List.Contains then tests to see if the value 401 is in the list.

if List.Contains(Text.Split([Line],","),"401") then 1 else null

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Is that your excepted result?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEw1DExMAJiY6VYHRDfFMQGYnMgtoCKgfggcRMdE0MDIG0G0qMUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Contains(Text.Split([Column1],","),"401") then 1 else null)
in
    #"Added Custom"

[Anonymous · Accepted solution]

Add the following formula to a customer column. It splits the line into a list based on a comma. List.Contains then tests to see if the value 401 is in the list.

if List.Contains(Text.Split([Line],","),"401") then 1 else null