Search code examples
jsonpowerbipowerquerypowerbi-desktopm

Handle input data and convert to easier format - PowerBI

This is a follow-up to my previous question which was answered by @SamNseir - thanks again!

I found another type of data in my original source column: several JSON array, comma-separated ....

Example:

Answer
-----------------------------
["German","English","French"]
["Ja","Yes","Oui"]
["A","B","C"],["A1","B1","C1"],["A2","B2","C2"]
0
Bern

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)
  • 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:

Answer
------
German
Ja
A,A1,A2
0
Bern

Any ideas? I'm still trying to get the hang of doing this kind of stuff in PowerBI ...

Solution

  • Not the most elegant but works.

    enter image description here

    enter image description here

    if Text.Contains ([Answer],"],[") then  Text.Combine( List.Transform( List.RemoveFirstN( Text.Split([Answer], "["""),1), each Text.BeforeDelimiter( _, """,")),",") else
try Json.Document([Answer]){0} otherwise [Answer]