Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Extracting List Values when single values are present

Hi, I am trying to find a way to extract list values in a column, but the situation is unique. Some cells contain a list with multiple values, some contain a single value, and some contain a blank v...

v-diye-msft
6 years ago
Hi Anonymous

You might refer to these similar posts:

https://community.powerbi.com/t5/Desktop/Extracting-a-list-of-key-value-pairs-into-multiple-columns/m-p/381676

https://community.powerbi.com/t5/Desktop/Extracting-the-first-comma-separated-value-to-a-new-column/m-p/226841

https://community.powerbi.com/t5/Desktop/Flattening-Rows-Containing-Values-and-Text-Dynamically/m-p/351128
ImkeF
6 years ago
Hi Anonymous ,

you can apply the following transformation on your column to transform everything in it to a list:

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type list) then _ else {_} }} )

this blogpost explains it: https://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi-and-power-query-in-excel/

Anonymous

Not applicable

6 years ago

Thanks. When I tried to split delimiter in Edit Queries, I get this error:

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

Still looking into the other resources you sent.

Community Champion

6 years ago

Hi Anonymous

You are encountering this error because you are trying to split the columns that contains null values.

You can replace null with any value then try splitting again based on your conditions