MS PowerQuery Export with Multi-Select Column - Delimiter


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?).


Best Answer