I have some data from a data source (which I can’t influence) which I’ve imported into PowerBI (using "Import"), and one column in particular (answers to questions) returns varying data – sometimes it’s a JSON array with three options, but sometimes it’s also data directly.
Example:
Answer
-----------------------------
["German","English","French"]
["Ja","Yes","Oui"]
0
1
3006
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 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
0
1
3006
Bern
But somehow, I just can’t seem to get the hang of it. How can I "conditionally" pick the first value from a JSON array or just copy the data (if it’s not a JSON array)? Any ideas? I’m still trying to get the hang of doing stuff in PowerBI ….
>Solution :
Add a new custom column with:
try Json.Document([Answer]){0} otherwise [Answer]
Or, if you want to update the same column:
= Table.TransformColumns(#"Previous Step", {"Answer", each try Json.Document(_){0} otherwise _})