Forum Discussion

tilldunk's avatar
tilldunk
Frequent Visitor
9 years ago
Solved

Converting JSON data into PowerBI table

Hello,   I got some data from IOT table storage including a column with JSON data, as below.     I want to extract the data of the fourth column and started with expanding the content and ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi tilldunk,

    I try to test bu getting date from a json file. I import the data and right click the record column->To table as follows.



    Then click expanded columns, get the list column, then click "Expand to New Rows"-> expanded column, I get expected result.



    Here is my Query statement.

    let
    Source = Json.Document(File.Contents("C:\Users\v-huizhn\Downloads\generated.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UserId", "id", "tipSecurableSet"}, {"Column1.UserId", "Column1.id", "Column1.tipSecurableSet"}),
    #"Expanded Column1.tipSecurableSet" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.tipSecurableSet", {"id", "Clients"}, {"Column1.tipSecurableSet.id", "Column1.tipSecurableSet.Clients"}),
    #"Expanded Column1.tipSecurableSet.Clients" = Table.ExpandListColumn(#"Expanded Column1.tipSecurableSet", "Column1.tipSecurableSet.Clients"),
    #"Expanded Column1.tipSecurableSet.Clients1" = Table.ExpandRecordColumn(#"Expanded Column1.tipSecurableSet.Clients", "Column1.tipSecurableSet.Clients", {"@odata.type", "typeId", "Entityids", "Clientid"}, {"[email protected]", "Column1.tipSecurableSet.Clients.typeId", "Column1.tipSecurableSet.Clients.Entityids", "Column1.tipSecurableSet.Clients.Clientid"})
in
    #"Expanded Column1.tipSecurableSet.Clients1"


    For your scenario, I can't reproduce it. Do you mind share the sample table for further analysis?

    Best Regards,
    Angelia

apo1979prio's avatar
apo1979prio
Icon for Helper I rankHelper I
5 years ago

Hello!!!
Only 7 Steps for GET JSON:
let
Source = SharePoint.Files("https://Mysharepoint", [ApiVersion = 15]),
Step01 = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://Mysharepoint_SearchFolder/")),
Step02 = Table.SelectColumns(Step01, {"Content", "Name", "Extension", "Folder Path"}),
Step03 = Table.SelectRows(Step02, each [Extension] = ".json"),
Step04 = Table.SelectColumns(Step03, {"Content"}),
Step05 = Table.AddColumn(Step04, "Json", each Json.Document([Content], 65001)),
Step06 = Table.SelectColumns(Step05, {"Json"}),
Step07 = Table.ExpandListColumn(Step06, "Json")
in
Step07