Forum Discussion

tilldunk's avatar
tilldunk
Frequent Visitor
8 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 then parsed JSON.

 

 

What I got is, as you can see, a column of lists. I can now add every single list as a new query and get the format (visuable data) I want:

 

 

However, my goal is to convert the whole column of lists at once as it is pretty time consuming to extract the values row by row

(by adding the lists as new queries and finally appending the aggregated queries to a new table).

 

 

My question: is there any simple step/code (advanced editor) I do not know, because I will have to deal with a lot more rows as only 18 in this example.

 

Any help will be highly appreciated, 

best regards,

Till

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 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

6 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Hi Till,

    did you try to click on the expand-buttons?:

     

     

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 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
    Helper 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