Forum Discussion
Extract List Data with Nulls
I'm trying to extract data from a list:
I'm using the built-in tools via Desktop, however, running into some issues. When I use the extract values from list tool and add my delimiter for concatenating list values, it is leaving out the null values. I need it to include any null values as it's throwing off all my columns once it's created. This is the query that it generates to extract the values:
= Table.TransformColumns(#"Expanded Value.rows", {"Value.rows", each Text.Combine(List.Transform(_, Text.From), "$"), type text})
Thanks for any help!
You may want to use one of the core Combiner functions instead of the "user friendly" Text.Combine.
If I didn't make mistakes, the following should work (but I can't test it):
= Table.TransformColumns(#"Expanded Value.rows", {"Value.rows", each Combiner.CombineTextByDelimiter("$")(List.Transform(_, Text.From)), type text})
5 Replies
Can you supply sample data to test with?
Here is what is in the list value:
1386446
2016-01-04T08:13:05Z
2016-01-04T13:51:03Z
A new case has been added.
null
null
null
null
But the extract only supplies:
1386446$2016-01-04T08:13:05Z$2016-01-04T13:51:03Z$A new case has been added. It's ignoring the last four nulls. I've been trying something as simple as a replace for all the nulls to "BLANK", but it doesn't seem to want to do it when in the list.
It becomes problematic as newer items in the list may look like:
1386446
2016-01-04T08:13:05Z
2016-01-04T13:51:03Z
A new case has been added.
null
Customer
Troubleshooting
null
or
1386446
2016-01-04T08:13:05Z
2016-01-04T13:51:03Z
A new case has been added.
null
null
Troubleshooting
null
So the columns get thrown off depending on which list item is being concatenated. Hence why I need the nulls included.
You may want to use one of the core Combiner functions instead of the "user friendly" Text.Combine.
If I didn't make mistakes, the following should work (but I can't test it):
= Table.TransformColumns(#"Expanded Value.rows", {"Value.rows", each Combiner.CombineTextByDelimiter("$")(List.Transform(_, Text.From)), type text})
