Extract List Data with Nulls

[dstanisljevic]

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!

[Greg_Deckler]

Can you supply sample data to test with?

[dstanisljevic]

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.

[MarcelBeug · Accepted solution]

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})

[areynolds]

Thanks for this little 'adjustment' save me from a headache

[dstanisljevic]

That's working. Awesome, thank you!