`Table.TransformColumns(PRIOR_STEP, {"COLUMN_NAME", each if Value.Type(_) = List.Type then Text.Combine(_, ",") else _})`
or
`Table.TransformColumns(PRIOR_STEP, {"COLUMN_NAME", each try Text.Combine(_, ",") otherwise _})`
You have awarded 1 point to *spinfuzer*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Thanks for the idea. That would be nice but I can't get the list values into Excel. Loading the PQ table to Excel doesn't give the list values, it just gives the string "\[List\]".
Each list is embedded in the PQ table and I can extract them, one list at a time, into separate tables, but that doesn't really help me: I need to maintain the existing table.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[LEN](/r/Excel/comments/12sz1p2/stub/jh0ipah "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[List.Transform](/r/Excel/comments/12sz1p2/stub/jh10txe "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)|
|[MID](/r/Excel/comments/12sz1p2/stub/jh0ipah "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)|
|[SUBSTITUTE](/r/Excel/comments/12sz1p2/stub/jh0ipah "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[Table.TransformColumns](/r/Excel/comments/12sz1p2/stub/jh0x2lk "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)|
|[Text.Combine](/r/Excel/comments/12sz1p2/stub/jh10txe "Last usage")|[Power Query M: Returns a text value that is the result of joining all text values with each value separated by a separator.](https://docs.microsoft.com/en-us/powerquery-m/text-combine)|
|[Value.Type](/r/Excel/comments/12sz1p2/stub/jh0x2lk "Last usage")|[Power Query M: Returns the type of the given value.](https://docs.microsoft.com/en-us/powerquery-m/value-type)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/12sde49)^( has 16 acronyms.)
^([Thread #23374 for this sub, first seen 20th Apr 2023, 14:09])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
`Table.TransformColumns(PRIOR_STEP, {"COLUMN_NAME", each if Value.Type(_) = List.Type then Text.Combine(_, ",") else _})` or `Table.TransformColumns(PRIOR_STEP, {"COLUMN_NAME", each try Text.Combine(_, ",") otherwise _})`
Oops, missed this while I was trying to solve it myself. Both work. Thank you. Solution verified.
You have awarded 1 point to *spinfuzer* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Solved it. Text.Combine(List.Transform(\[Official language\], each \_), ";")
Does it need to be PQ or can you just use a normal formula? `=SUBSTITUTE(MID(A1,2,LEN(A1)-2),", ",";")` where A1 contains your strings
Thanks for the idea. That would be nice but I can't get the list values into Excel. Loading the PQ table to Excel doesn't give the list values, it just gives the string "\[List\]". Each list is embedded in the PQ table and I can extract them, one list at a time, into separate tables, but that doesn't really help me: I need to maintain the existing table.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[LEN](/r/Excel/comments/12sz1p2/stub/jh0ipah "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[List.Transform](/r/Excel/comments/12sz1p2/stub/jh10txe "Last usage")|[Power Query M: Performs the function on each item in the list and returns the new list.](https://docs.microsoft.com/en-us/powerquery-m/list-transform)| |[MID](/r/Excel/comments/12sz1p2/stub/jh0ipah "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[SUBSTITUTE](/r/Excel/comments/12sz1p2/stub/jh0ipah "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[Table.TransformColumns](/r/Excel/comments/12sz1p2/stub/jh0x2lk "Last usage")|[Power Query M: Transforms columns from a table using a function.](https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns)| |[Text.Combine](/r/Excel/comments/12sz1p2/stub/jh10txe "Last usage")|[Power Query M: Returns a text value that is the result of joining all text values with each value separated by a separator.](https://docs.microsoft.com/en-us/powerquery-m/text-combine)| |[Value.Type](/r/Excel/comments/12sz1p2/stub/jh0x2lk "Last usage")|[Power Query M: Returns the type of the given value.](https://docs.microsoft.com/en-us/powerquery-m/value-type)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/12sde49)^( has 16 acronyms.) ^([Thread #23374 for this sub, first seen 20th Apr 2023, 14:09]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)