Forum Discussion
Splitting multi-element data structure list into separate rows
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.
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"
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.