none
Transforming json data from web using power query RRS feed

  • Question

  • Hi All,

    I'm totally a newbie in using power query, but managed to use it with the help of some introductory tutorial.

    Now I'm trying to transform some data exposed as JSON feed from my web application. Expect in one column, due to nature of data returned by the json feed. 

    While converting the source into to table format one of the columns [Column1 hours, please the "Notes" below] shows shows two types of data - record and list. I this case, with my limited exposure, I'm not able to get the values due to this mix-up (this is my assumption). Any pointers to solve this issue is greatly appreciated as I'm stuck. 

    List

    Record

    Record

    List

    Record

    Record

    List

    Thanks in advance

    Note : This is what I can see in the advanced editor (the source is edited)

    let
        Source = Json.Document(Web.Contents("http://www.acmee.com/feeds/mylisting.all.json")),
        #"Converted to Table" = Record.ToTable(Source),
        Value = #"Converted to Table"{1}[Value],
        #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"name", "latitude", "hours"}, {"Column1.name", "Column1.latitude", "Column1.hours"}),
        #"Expanded Column1.hours" = Table.ExpandListColumn(#"Expanded Column1", "Column1.hours"),
        #"Column1 hours" = #"Expanded Column1.hours"{0}[Column1.hours]
    in
        #"Column1 hours"




    • Edited by kalladasan Thursday, November 26, 2015 1:09 AM
    Wednesday, November 25, 2015 9:49 PM

Answers