Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    v-frfei-msft
    6 years ago

    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"

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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"