MS PowerQuery Export with Multi-Select Column - Delimiter

Steve Monk

Hello,

Part of our process requires data from smartsheet to be put into excel for which we use PowerQuery (and this works very well). However, we have lately encountered a problem in that Multi-Select Columns are output into the resulting excel table with a comma delimiting each value. It would seem this this is a logical approach - however, in some cases the multi select column has options that themselves contain commas, as a result when this information finds its way into the excel document we cannot successfully parse the data using a "comma" as the delimiter as this splits the data into incorrect options.

For example (only an example - not actual data) - assume in SmartSheet we have choices of "Option 1, Good" and "Option 2, Better" - when this comes into excel we end up with "Option 1, Good, Option 2, Better" - so if we try to parse this to extract the differently selected values we cannot - as we end up with "Option 1" "Good" "Option 2" "Better" - we get four results instead of two.

Does anyone have any idea if it is possible to adjust which delimiter smartsheet uses when outputting multi-select column values? Alternatively I could do something on the PowerQuery side - if there is a way for me to be able to identify distinct values from smartsheet before they find their way into a cell (any one have any idea how to do that?).

Thanks!

Paul Newcome

The built in delimiter in Smartsheet is CHAR(10) which is a line break. I'm not sure if you can leverage that on the PowerQuery side of things, but you can on the Smartsheet side.

If you insert a text number column, you can use a SUBSTITUTE function to replace the delimiter with a delimiter of your choice. Since you are able to specify the delimiter and the output is a text string nothing should change when it gets put into Excell.

=SUBSTITUTE([Multi-Select Dropdown Column]@row, CHAR(10), "delimiter of choice")

Paul Newcome

The built in delimiter in Smartsheet is CHAR(10) which is a line break. I'm not sure if you can leverage that on the PowerQuery side of things, but you can on the Smartsheet side.

If you insert a text number column, you can use a SUBSTITUTE function to replace the delimiter with a delimiter of your choice. Since you are able to specify the delimiter and the output is a text string nothing should change when it gets put into Excell.

=SUBSTITUTE([Multi-Select Dropdown Column]@row, CHAR(10), "delimiter of choice")

Steve Monk

Ah - what a great idea! I had not considered creating a calculated column to contain the same information - but substituting a character of choice. thanks for letting me know that CHAR(10) (line feed?) is the standard multi-select delimiter.

This approach will work - thanks!!

Paul Newcome

Happy to help. 👍️

Side note: The default delimiter for multi-select contact columns is ", " (comma space). It can't be used to output multiple contacts, but it can be leveraged for similar cases such as the above.