PowerBI - handling column with mixed bag of data - level 3

Question

This is another follow-up to my previous question - another degree of difficulty has shown up, and I'm at a loss on how to deal with it.

I found another type of data in my original source column: several JSON arrays, comma-separated - and to make things more "interesting", the comma-separated strings actually have commas inside them.....

Example:

DataColumn
-----------------------------
["German","English","French"]
["A","B","C"],["A1","B1","C1"],["A2","B2","C2"]
["A1, A2, A3","B1, B2, B3","C1, C2"],["D1, D2","E1","F1, F2, F3, F4"],["G1,G2,G3","H1, H2, H3","J1, J2"]
0

I would like to "convert" this mixed bag of data so that

• if it really is a JSON array, I want to take the first value (always)
  • in case the string contains comma-separated values inside it - take the whole string - separate the n possible strings (which might contains commas) with semicolons
• if it is a list of JSON arrays (comma-separated), I want to get the first of each array, and show them comma-separated
• if it is NOT a JSON array, I just want to take the data unchanged

So in the end, I'd like to have this:

DataColumn
----------
German
A, A1, A2
A1, A2, A3;D1, D2;G1,G2,G3
0

Any ideas? I tried to use Davide Bacci's response to the previous question - but I still cannot make sense of DAX / PowerQuery and PowerBI functions and stuff, so I wasn't able to adapt this solution from the previous answer to this additional requirement....

Answer 1

Does this work? Replace "[" with "{" and "]" with "}" and then turn the column into list of lists. Then try to take the first element of each list and text combine it. If that fails take the original value.

Only thing you need to change below is the [DataColumn] after replacing the [ ]

let L = Expression.Evaluate("{" & [DataColumn] & "}")
in try Text.Combine(List.Transform(L, each _{0}),";") otherwise L{0}

Answer 2

if Text.Contains ([Answer],"],[") then
let a = Text.Split([Answer], "["""),
b = List.RemoveFirstN(a, 1), 
c = List.Transform(b, each Text.BeforeDelimiter( _, """")),
d = List.MatchesAny(c, each Text.Contains( _ , "," )), 
e = if d =false then Text.Combine(c,",") else  Text.Combine(c, ";")

in e
else try Json.Document([Answer]){0} otherwise [Answer]