MS PowerQuery Export with Multi-Select Column - Delimiter

Steve Monk
Steve Monk ✭✭
edited 06/14/22 in Add Ons and Integrations

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!

Best Answer

Answers