MS PowerQuery Export with Multi-Select Column - Delimiter
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
-
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")
Answers
-
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")
-
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!!
-
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.
