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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
garycarters
Helper I
Helper I

Splitting multi-element data structure list into separate rows

Hi all

 

I have a table that contains a number of control records.  Each control record can dictate the behaviour of an aspect of our system and may be a string, flag, file name or a list of data.  My question relates to records defining lists of data.

 

The data lists comprise a prefix ("List:") followed by a list of indexes and descriptions.  For example;

List:"1","Description 1", "1","Description 1", "2","Description 2", "3","Description 3", etc

 

I want to access those descriptions based on the index used by the customer record - each customer is attached to an index on the customer record.

 

How can I split the list into a table of index, description?  I've tried using Split Column by delimiter which kind of works but gives a row in the output for each element;

1

Description 1

2

Description 2

3

Description 3

etc

 

I just want

1        Description 1

2        Description 2

3        Description 3

etc

 

I can then join the customer index to the description index.

 

TIA

1 ACCEPTED SOLUTION

Hi @garycarters 

 

1. Create new Source >> Blank Query 

2. Go to Advanced Editor and paste the Function Code Provided.

3. In your table, go to Add Column ribbon and find Invoke Custom Function 

4. In the Function Query section select the function you have created (most probably Query1 if you have not change the name ).

5. In Your Column Section change ABC to Column Name and Select ListData from the drop down list next press OK to confirm.

6. Now you should have an extra column in your table that can be expanded by:

  • Clicking inside the cell on "Table" 
  • If you want to expand on the original table, click on the double arrow on the column header and select the columns you want to add
Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

  

 

View solution in original post

11 REPLIES 11
Mariusz
Community Champion
Community Champion

Hi @garycarters 

 

You can use the below M syntax 

let
    Source = {"1","Description 1", "1","Description 1", "2","Description 2", "3","Description 3"},
    Split = List.Split( Source,  2 ), 
    Output = #table( type table [ ID = Int64.Type, Description = text ], Split )
in
    Output

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @Mariusz: Good day. I saw this post. I have an issue with a list, "locations" I want to split into 2 Columns: locationId and locationName. I have posted this on:

https://community.powerbi.com/t5/Desktop/Split-a-Single-Column-with-delimiter-colon/m-p/1430799/high...

Many thanks in advance.

Hi Mariusz

 

Thanks for that, it looks promising.

 

I've entered the code as below.  However, 

= let
    Source = Sql.Database("calsql", "mcsrm_live"),
    dbo_spec_checklist_items = Source{[Schema="dbo",Item="spec_checklist_items"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_spec_checklist_items, each ([id] = 57)),
    #"Extracted Text Range" = Table.TransformColumns(#"Filtered Rows", {{"ListData", each Text.Middle(_, 5, 200000), type text}}),
    Split = List.Split( "Extracted Text Range", 2 ),
    Output = #table( type table [ ID = Int64.Type, Description = text ], Split )
in
    #"Extracted Text Range"

However, when I preview the results it's still only showing a single record.  

 

I'm obviously missing something simple...

 

Hi @garycarters 

My bad I think I missed couple of steps  

let
    Source = " List:""1"",""Description 1"", ""1"",""Description 1"", ""2"",""Description 2"", ""3"",""Description 3""",
    #"Text Clean1" = Text.Replace(Source, """", ""),
    #"Text Clean2" = Text.Replace( #"Text Clean1", "List:", ""),
    #"Text Split" = Text.Split( #"Text Clean2", "," ),
    #"List Split" = List.Split( #"Text Split", 2 ),
    #"List to Table" = #table( type table [ ID = Int64.Type, Description = text ], #"List Split" ),
    #"Changed Type" = Table.TransformColumnTypes(#"List to Table",{{"ID", Int64.Type}})
in
    #"Changed Type"

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

We're getting closer.

 

The code below gives error 

"Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=Table
Type=Type"

 

I think the misunderstanding is the list data is coming from a column in table spec_checklist_items, your example has a string. 

 

 

let
    Source = Sql.Database("calsql", "mcsrm_live"),
    dbo_spec_checklist_items = Source{[Schema="dbo",Item="spec_checklist_items"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_spec_checklist_items, each ([id] = 57)),
    #"Text Clean1" = Text.Replace(#"Filtered Rows", """", ""),
    #"Text Clean2" = Text.Replace( #"Text Clean1", "List:", ""),
    #"Text Split" = Text.Split( #"Text Clean2", "," ),
    #"List Split" = List.Split( #"Text Split", 2 ),
    #"List to Table" = #table( type table [ ID = Int64.Type, Description = text ], #"List Split" ),
    #"Changed Type" = Table.TransformColumnTypes(#"List to Table",{{"ID", Int64.Type}})
in
    #"Changed Type"

 

Hi @garycarters 

 

Can you send a sample of your table with fiew rows?

 

Thanks
Mariusz

It's a single record, containing the delimited list of id's and descriptions in one field plus various other bits of information I'm not interested in.

Hi @garycarters 

I've converted it into a function, you can invoke it in your table and pass the column as a parameter.

(#"Your Column" as text ) => let
    #"Text Clean1" = Text.Replace(#"Your Column", """", ""),
    #"Text Clean2" = Text.Replace( #"Text Clean1", "List:", ""),
    #"Text Split" = Text.Split( #"Text Clean2", "," ),
    #"List Split" = List.Split( #"Text Split", 2 ),
    #"List to Table" = #table( type table [ ID = Int64.Type, Description = text ], #"List Split" ),
    #"Changed Type" = Table.TransformColumnTypes(#"List to Table",{{"ID", Int64.Type}})
in
    #"Changed Type"

 

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'd never have got there without your help!  That works beautifully.

 

Thanks Mariusz

Sorry been away for a couple of days.

 

My Table query is now this;

let
    Source = Sql.Database("calsql", "mcsrm_live"),
    dbo_spec_checklist_items = Source{[Schema="dbo",Item="spec_checklist_items"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_spec_checklist_items, each ([id] = 57)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"charge_item", "doc_path", "used_for_equipment", "used_for_contracts", "used_for_works_orders", "view_from_web", "used_for_customers", "used_for_suppliers", "used_for_employees", "SpecificationAutoCreate", "SpecificationMandatory", "UsedForPurchaseOrders", "UsedForPATTests", "UsedForQuotes", "UsedForPODistributions", "UsedForContacts", "UsedForProspects", "UsedForInvoices", "Redact", "UsedForJobs", "UsedForCases"}),
    #"Extracted ListData" = Table.TransformColumns(#"Removed Columns", {{"ListData", each Text.Middle(_, 5, 200000), type text}})
in
    #"Extracted ListData"

Column ListData now contains the list of values I wish to break into Rows of 2 columns (ID and description).

 

I've created a custom column with the code you supplied

 

Capture.PNG

How do I invoke the function you have provided from here to give me the rows / columns I originally outlined?  This is all new (but very exciting) ground for me.

Hi @garycarters 

 

1. Create new Source >> Blank Query 

2. Go to Advanced Editor and paste the Function Code Provided.

3. In your table, go to Add Column ribbon and find Invoke Custom Function 

4. In the Function Query section select the function you have created (most probably Query1 if you have not change the name ).

5. In Your Column Section change ABC to Column Name and Select ListData from the drop down list next press OK to confirm.

6. Now you should have an extra column in your table that can be expanded by:

  • Clicking inside the cell on "Table" 
  • If you want to expand on the original table, click on the double arrow on the column header and select the columns you want to add
Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

  

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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