Expanding items in 'List' in column when some cells are blank

[tomsaxton10]

I am trying to expand the items in 'List' out as they form part of a PowerBI table. Currently, that table is just showing 'List' rather than the separate items that sit within list. 

Any suggestions?

View of the columnList items

[Anonymous · Accepted solution]

Hi @tomsaxton10 ,

Please follow these steps:

1. Right-click the List --> Select To Table

2. Add a Custom column:

= if [Column1]="" then {""} else [Column1]

3. Click the Expand icon-->Expand to New Rows:

4. Remove the original column and filter out blank rows:

Final output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomsaxton10]

Thanks for your response Evelyn. For some reason, I don't have the option to select 'To table' when I right-click the list. Any thoughts as to why that might be the case as I am stumped! 

Also, I don't want to filter out the blank rows as I am counting the data in those blank rows for a different part of the PowerBI dashboard. When I filter out blanks, it removes them and will impact the data on my dashboard. 

[Anonymous]

Hi @tomsaxton10 ,

Please try to create a blank query and paste the below M syntax in Advanced Editor dialog:

let
    Source ={
"","","",{"Technical","Operational","Legal"},"","",{"Desktop","Service"},{"Milk","Egg","Bread","Rice"},{"Mouse","Keyboard"},""
},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1]="" then {""} else [Column1]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column1"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin

[BA_Pete]

Hi @tomsaxton10 ,

You could try using Table.ExpandListColumn:

Table.ExpandListColumn(previousStep, "Outcome Category")

Pete

[tomsaxton10]

Hi Pete,

Thanks for the quick response. I have tried this (I think) but got the following error message. 

[BA_Pete]

@tomsaxton10 ,

You could try just running the code I gave you as a custom step, rather than a new column, but I suspect the issue is actually due to the blank rows.

Add  a new custom column like this to generate a column that can be expanded using the GUI:

if [Outcome Category] = "" then {99999..99999} else [Outcome Category]

Substitute the '99999's with any number you want, as long as both entries are the same.

Pete

[AlexisOlson]

Yeah, I think it would work with nulls rather than empty strings, as mentioned in these posts:
https://community.powerbi.com/t5/Power-Query/SharePoint-List-and-null-values/m-p/1763308

https://community.powerbi.com/t5/Desktop/Custom-columns-in-a-SharePoint-Page-Library/m-p/2176571

These use a column transform with this function:

if Value.Is(_, type list) then _ else null

and then expand.