Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😕
Solved! Go to Solution.
Hi @bcoulam
You might refer to these similar posts:
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...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @bcoulam
I cannot spot where you used my code.
What am I missing here?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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!
Great! Pleased to hear 🙂
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @bcoulam
You might refer to these similar posts:
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |