Search code examples
powerbidata-conversion

Trying to convert a column in PowerBI

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]

    enter image description here

    Or, if you want to update the same column:

    = Table.TransformColumns(#"Previous Step", {"Answer", each try Json.Document(_){0} otherwise _})