Search code examples
jsonparsingpowerbipowerquerym

How to split JSON columns in Power BI

enter image description hereenter image description here

I have imported JSON data from Hive database. The structure looks like the attached. JSON data has been dumped to Hive without normalizing. Is it possible to parse the data?. For example, in the attached image, the mentionedlocations column has some places mentioned and I want them to be in separate rows.

Solution

  • You can use the Json.Document function to read the column as JSON.

    I'd suggest creating a custom column with this formula:

    Record.ToTable(Json.Document([mentionedlocations]))

    and then expanding that column to get the multiple rows you want.

    Putting these together:

    = Table.ExpandTableColumn(
      Table.AddColumn(PreviousStep, "Custom",
          each Record.ToTable(Json.Document([mentionedlocations]))),
      "Custom", {"Name"}, {"locations"})

    This takes the PreviousStep in the query, adds a Custom column which converts the JSON text into a table and then expands the Name column in each of the tables in the Custom column and renames the column locations.