Forum Discussion
How to expand list column which has empty data
I found a simple solution for this . This will save others friend's lot of time. If Microsoft added this step as part of ExpandListColumn, would be great.
Solution:
//Just replace the empty value to empty list as below, that is it.
#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","",{""},Replacer.ReplaceValue,{"order_id"}),
//Then expand the column and refresh without that error.
#"Expanded" = Table.ExpandListColumn(#"Replaced Value", "order_id")
Hi ykannan
There is a fill-down option in the power query, Can you try that and check.
Note: the cells below c2 should be blank in order for this to work. If it note use the replace option in power query to replace it will null
If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,
Atma.
Hi Dhacd , Thanks for your reply.
Order_id column has list data. Fill_down did not work for that.
The list is empty for some rows and some has value. When I expand and the refresh, I am getting error as empty object cannot be converted. So in M code, I want to check for empty check, if not empty then that many rows should get created based on th list count.I found a simple solution for this . This will save others friend's lot of time. If Microsoft added this step as part of ExpandListColumn, would be great.
Solution:
//Just replace the empty value to empty list as below, that is it.
#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","",{""},Replacer.ReplaceValue,{"order_id"}),
//Then expand the column and refresh without that error.
#"Expanded" = Table.ExpandListColumn(#"Replaced Value", "order_id")Genius!
