Extract JSON data

[Anonymous]

Hi ,

I have a JSON string that i want to extract in power bi. It is the following JSON string:

{
  "id": "...",
  "name": "...",
  "address": "...",
  "types": [
    "shopping_mall",
    "point_of_interest",
    "establishment"
  ],
  "coordinates": {
    "lat": ...,
    "lng": ...
  },
  "rating": 3.7,
  "rating_n": 1495,
  "international_phone_number": "...",
  "current_popularity": 35,
  "populartimes": [
    {
      "name": "Monday",
      "data": [
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        24,
        31,
        32,
        24,
        14,
        0,
        0,
        0,
        0,
        0,
        0
      ]
    },
  ],
  "time_spent": [
    15,
    60
  ]
}

The most important thing is the "populartimes": [...]. In this section I get the data of each day (times 7). And i want to extract this all in one column. I have come a whole end and got to this point (see picture 1)

What i want is the following thing, but than for eacht column. 

I have fold out this first column. But when I try to do this for the second column I get a lot more columns than the 24 I want to. What steps do I have to follow? Or is there a manuel that explains how to extract JSON data?

[mwegener · Accepted solution]

Hi @Anonymous ,

I think I have it, try the steps in this file.

PBIX

[Anonymous]

Thank you both for your replys! 

Both answers are correct and does what i wanted. The difference is that one is solved with code and the other with power bi extractions.

[Anonymous · Accepted solution]

HI @Anonymous,

I'm not so sure how your records stored, I copy your sample data and try to add more records, you can check below sample code if they suitable for your requirements.

Sample data:

Spoiler

{
   "id":"...",
   "name":"...",
   "address":"...",
   "types":[
      "shopping_mall",
      "point_of_interest",
      "establishment"
   ],
   "coordinates":{
      "lat":"...",
      "lng":"..."
   },
   "rating":3.7,
   "rating_n":1495,
   "international_phone_number":"...",
   "current_popularity":35,
   "populartimes":[
      {
         "name":"Monday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      },
      {
         "name":"Tuesday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      },
      {
         "name":"Wednesday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      },
      {
         "name":"Thursday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      },
      {
         "name":"Friday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      },
      {
         "name":"Saturday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      },
      {
         "name":"Sunday",
         "data":[
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            0,
            24,
            31,
            32,
            24,
            14,
            0,
            0,
            0,
            0,
            0,
            0
         ]
      }
   ],
   "time_spent":[
      15,
      60
   ]
}​

Power query formula:

let
    Source = Json.Document(File.Contents("C:\Users\xxxxx\Desktop\sample.json"))[populartimes],
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "data"}, {"name", "data"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Column1", "Merged", each Text.Combine(List.Transform([data],each Text.From(_)),",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"data"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Monday", type text}, {"Tuesday", type text}, {"Wednesday", type text}, {"Thursday", type text}, {"Friday", type text}, {"Saturday", type text}, {"Sunday", type text}})
in
    #"Changed Type"

Regards,

Xiaoxin Sheng

[mwegener · Accepted solution]

Hi @Anonymous ,

I think I have it, try the steps in this file.

PBIX