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
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....
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]