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

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]