Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Parsing embedded JSON into PowerBI table

Hi Everyone,  I have tried to follow the video on http://community.powerbi.com/t5/Desktop/Parsing-Embedded-JSON/m-p/118623/highlight/true#M50071   to get embedded json displayed. unfortunately, I ...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    Pls check if this delivers what you're after:

    let
    Source = Json.Document(<<YourJSON>>),
    query = Source[query],
    results = query[results],
    #"Converted to Table" = Record.ToTable(results),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}),
    #"Expanded printouts" = Table.ExpandRecordColumn(#"Expanded Value", "printouts", {"Has technique"}, {"Has technique"}),
    #"Expanded Has technique" = Table.ExpandListColumn(#"Expanded printouts", "Has technique"),
    #"Expanded Has technique1" = Table.ExpandRecordColumn(#"Expanded Has technique", "Has technique", {"fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"fulltext.1", "fullurl.1", "namespace.1", "exists.1", "displaytitle.1"})
in
    #"Expanded Has technique1"

     

    How to integrate M-code into your solution  -- Check out more PBI- learning resources here

ImkeF's avatar
ImkeF
Community Champion
9 years ago

Pls check if this delivers what you're after:

let
    Source = Json.Document(<<YourJSON>>),
    query = Source[query],
    results = query[results],
    #"Converted to Table" = Record.ToTable(results),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"printouts", "fulltext", "fullurl", "namespace", "exists", "displaytitle"}),
    #"Expanded printouts" = Table.ExpandRecordColumn(#"Expanded Value", "printouts", {"Has technique"}, {"Has technique"}),
    #"Expanded Has technique" = Table.ExpandListColumn(#"Expanded printouts", "Has technique"),
    #"Expanded Has technique1" = Table.ExpandRecordColumn(#"Expanded Has technique", "Has technique", {"fulltext", "fullurl", "namespace", "exists", "displaytitle"}, {"fulltext.1", "fullurl.1", "namespace.1", "exists.1", "displaytitle.1"})
in
    #"Expanded Has technique1"

 

How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hello ImKeF,

that was exactly what I was after, thank you very much.

Is it on purpose that PowerBI does not keep the old data set when you update the source?

 

regards