Extracting List Values when single values are present

[bcoulam]

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 value, as shown below. Is there a way to extract the list values so they display in their own columns? I suspect all cells in the column would need to be [list] but want to see if there are any ideas out there.

Could not insert screenshot for some reason 😕

[v-diye-msft · Accepted solution]

Hi @bcoulam 

You might refer to these similar posts:

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

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

https://community.powerbi.com/t5/Desktop/Flattening-Rows-Containing-Values-and-Text-Dynamically/m-p/... 

[ImkeF · Accepted solution]

Hi @bcoulam ,

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... 

[ImkeF]

Hi @bcoulam 

I cannot spot where you used my code. 

What am I missing here?

[bcoulam]

@ImkeF, thank you so much for your help with this! I went back and found the change to make with my code, and got it working. Here was my final code:

let

   Source = Json.Document(Web.Contents("https://zionsmgtit.my.workfront.com/attask/api/v9.0/project/search?portfolioID_Mod=5e0fb258028800f35... Project=Yes&OR:1:DE:BPCM Project_Mod=in&fields= DE:Current Status&fields=description&fields=owner:name&fields=DE:Customer Impact L2&fields=plannedStartDate&fields=DE:Employee Impact&fields=DE:Data&fields=DE:Reputation Risk&fields=DE:Process&fields=DE:Schedule Status&fields=DE:L1 Process Primary&fields=DE:L1 Process Secondary&fields=DE:Cyber&fields=DE:Internal Maturity&fields=DE:Legal/Regulatory risk&fields=DE:ITESC Approved&fields=DE:Total Investment/P%26L Impact&fields=DE:Change Initiative&fields=DE:Secondary Strategic Alignment&fields=DE:Primary Strategic Alignment&fields=DE:Risk Rating BCI&method=get&$$LIMIT=2000&apiKey=g3ce27r47eeiagbqitkwjlyvm842rb1m")),

   #"Converted to Table" = Record.ToTable(Source),

   #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),

   #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"ID", "name", "objCode", "DE:Current Status", "description", "owner", "DE:Customer Impact L2", "plannedStartDate", "DE:Employee Impact", "DE:Data", "DE:Reputation Risk", "DE:Process", "DE:Schedule Status", "DE:L1 Process Primary", "DE:L1 Process Secondary", "DE:Cyber", "DE:Internal Maturity", "DE:Legal/Regulatory risk", "DE:ITESC Approved", "DE:Total Investment/P&L Impact", "DE:Change Initiative", "DE:Secondary Strategic Alignment", "DE:Primary Strategic Alignment", "DE:Risk Rating BCI"}, {"Value.ID", "Value.name", "Value.objCode", "Value.DE:Current Status", "Value.description", "Value.owner", "Value.DE:Customer Impact L2", "Value.plannedStartDate", "Value.DE:Employee Impact", "Value.DE:Data", "Value.DE:Reputation Risk", "Value.DE:Process", "Value.DE:Schedule Status", "Value.DE:L1 Process Primary", "Value.DE:L1 Process Secondary", "Value.DE:Cyber", "Value.DE:Internal Maturity", "Value.DE:Legal/Regulatory risk", "Value.DE:ITESC Approved", "Value.DE:Total Investment/P&L Impact", "Value.DE:Change Initiative", "Value.DE:Secondary Strategic Alignment", "Value.DE:Primary Strategic Alignment", "Value.DE:Risk Rating BCI"}),

   #"Transform Column" = Table.TransformColumns(#"Expanded Value1", {{"Value.DE:L1 Process Secondary", each if Value.Is(_, type list) then _ else {_} }} )

in

#"Transform Column"

I'm thrilled with this solution!

[ImkeF]

Great! Pleased to hear 🙂

[v-diye-msft · Accepted solution]

Hi @bcoulam 

You might refer to these similar posts:

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

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

https://community.powerbi.com/t5/Desktop/Flattening-Rows-Containing-Values-and-Text-Dynamically/m-p/... 

[bcoulam]

@v-diye-msft 

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.

[mussaenda]

Hi @bcoulam 

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

[VasTg]

@bcoulam 

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

[VasTg]

@bcoulam 

Did you try Split columns with delimiter option in Edit queries?