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.

Memorable Member

6 years ago

Anonymous

Please post sample data and screenshot of what you have tried so far to better resolve the problem.