How to Expand a List within a column where there are both Lists and Text

[RPeruski]

I have some data that I extracted from a JSON which loaded my data into a single column with some of the data being text and some of the data being lists within the same column. Typically when a column has lists the drop down at the column header has the icons to expand the column, because the list contains both text & list values, the drop down button only lets me filter to the text values. 

Is there a method to either deliminate the list values within the single column or to break into new columns? For reference I've tried to split column by deliminater and that hasn't worked. 

[MFelix]

Hi @RPeruski ,

Since you have the column being a JSON you can do the parse as JSON in the Power Query:

https://support.microsoft.com/en-us/office/parse-text-as-json-or-xml-power-query-7436916b-210a-4299-...

[RPeruski]

The column with list and text values is after I've parsed as JSON in Power Query and then expanded the values as new columns.

[MFelix]

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[RPeruski]

Here is a mock up of what the data looks like.

Car Failures Reported Per Customer

Engine

Transmission

List

Brakes

Then the List would contain the following 

Engine

Brakes

[MFelix · Accepted solution]

Hi @RPeruski 

Try to add a new column with following code:

if Value.Is([Car Failures Reported Per Customer], type list) 
                then Text.Combine(List.Transform([Car Failures Reported Per Customer], Text.From), ",")
           else [Car Failures Reported Per Customer]

This will give you something similar to this:

Then you can make split columns to rows based on the comma.

[RPeruski]

Success! Thank you!