Forum Discussion

John343467's avatar
John343467
Icon for Helper I rankHelper I
5 years ago
Solved

Expanding Json list

Hi, 

 

I'm not sure how to do this. I'm currently accessing this web api in powerbi. 

https://www.tomtom.com/en_gb/traffic-index/page-data/stuttgart-traffic/page-data.json

 

I'm trying to list the values per year (available years). 

 

In the advanced editor, I've the following: 

let
Source = Json.Document(Web.Contents("https://www.tomtom.com/en_gb/traffic-index/page-data/" & CityCode & "-traffic/page-data.json")),
result = Source[result],
data = result[data],
citiesJson = data[citiesJson],
Custom1 = Table.FromRecords( { Source} ),
#"Expanded result" = Table.ExpandRecordColumn(Custom1, "result", {"data", "pageContext"}, {"result.data", "result.pageContext"}),
#"Expanded result.data" = Table.ExpandRecordColumn(#"Expanded result", "result.data", {"citiesJson"}, {"result.data.citiesJson"}),
#"Expanded result.data.citiesJson" = Table.ExpandRecordColumn(#"Expanded result.data", "result.data.citiesJson", {"key", "circleKey", "name", "country", "countryName", "position", "circle", "timezone", "availableYears", "roadNetwork", "stats2019", "stats2018", "stats2017"}, {"result.data.citiesJson.key", "result.data.citiesJson.circleKey", "result.data.citiesJson.name", "result.data.citiesJson.country", "result.data.citiesJson.countryName", "result.data.citiesJson.position", "result.data.citiesJson.circle", "result.data.citiesJson.timezone", "result.data.citiesJson.availableYears", "result.data.citiesJson.roadNetwork", "result.data.citiesJson.stats2019", "result.data.citiesJson.stats2018", "result.data.citiesJson.stats2017"}),
#"Expanded result.data.citiesJson.stats2017" = Table.ExpandRecordColumn(#"Expanded result.data.citiesJson", "result.data.citiesJson.stats2017", {"rank", "congestion", "results"}, {"result.data.citiesJson.stats2017.rank", "result.data.citiesJson.stats2017.congestion", "result.data.citiesJson.stats2017.results"}),
#"Expanded result.data.citiesJson.stats2018" = Table.ExpandRecordColumn(#"Expanded result.data.citiesJson.stats2017", "result.data.citiesJson.stats2018", {"rank", "congestion", "delta", "results"}, {"result.data.citiesJson.stats2018.rank", "result.data.citiesJson.stats2018.congestion", "result.data.citiesJson.stats2018.delta", "result.data.citiesJson.stats2018.results"}),
#"Expanded result.data.citiesJson.stats2019" = Table.ExpandRecordColumn(#"Expanded result.data.citiesJson.stats2018", "result.data.citiesJson.stats2019", {"rank", "congestion", "delta", "results"}, {"result.data.citiesJson.stats2019.rank", "result.data.citiesJson.stats2019.congestion", "result.data.citiesJson.stats2019.delta", "result.data.citiesJson.stats2019.results"})
in
#"Expanded result.data.citiesJson.stats2019"

 

I cannot however expand the list or when I do, it just creates new rows. 

 

Does anyone know how I can get the results per year. Thanks for any help. 

 

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, John343467 , the solution is really verbose as requested fields lie in different layers of hierarchy. you might want to try this solution,

    let
    Source = Json.Document(Web.Contents("https://www.tomtom.com/en_gb/traffic-index/page-data/stuttgart-traffic/page-data.json")),

    tbl = Table.FromRecords({Source[result][data][citiesJson]}),
    #"Removed Columns" = Table.SelectColumns(tbl, {"name"} & List.Select(Table.ColumnNames(tbl), each Text.Contains(_,"stats"))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"name"}, "Year", "Record"),
    #"Extracted Yr" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Year", each Number.From(Text.Select(_,{"0".."9"})), type number}}),
    #"Expanded Record" = Table.ExpandRecordColumn(#"Extracted Yr", "Record", {"rank", "congestion", "results"}, {"rank", "congestion", "results"}),
    results = Table.TransformColumns(#"Expanded Record", 
        {
            {"results",
            each
                [
                    worst = [worstDay],
                    best = [bestDay],
                    res = [
                            #"worst Day/Month" = Text.From(worst[day])&"/"&Text.From(worst[month]),
                            #"worst Congestion" = Number.Round(worst[congestion]),
                            #"best Day/Month" = Text.From(best[day])&"/"&Text.From(best[month]),
                            #"best Congestion" = Number.Round(best[congestion])
                    ]
                ][res]
            }
        }
    ),
    #"Expanded results" = Table.ExpandRecordColumn(results, "results", {"worst Day/Month", "worst Congestion", "best Day/Month", "best Congestion"}, {"worst Day/Month", "worst Congestion", "best Day/Month", "best Congestion"})
in
    #"Expanded results"

     

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    John343467 I can help you to get the data if you tell me which data you want to pull in, there is a lot of data in that JSON, if you specify what columns you want, it will help.

      • CNENFRNL's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hi, John343467 , the solution is really verbose as requested fields lie in different layers of hierarchy. you might want to try this solution,

        let
    Source = Json.Document(Web.Contents("https://www.tomtom.com/en_gb/traffic-index/page-data/stuttgart-traffic/page-data.json")),

    tbl = Table.FromRecords({Source[result][data][citiesJson]}),
    #"Removed Columns" = Table.SelectColumns(tbl, {"name"} & List.Select(Table.ColumnNames(tbl), each Text.Contains(_,"stats"))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"name"}, "Year", "Record"),
    #"Extracted Yr" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Year", each Number.From(Text.Select(_,{"0".."9"})), type number}}),
    #"Expanded Record" = Table.ExpandRecordColumn(#"Extracted Yr", "Record", {"rank", "congestion", "results"}, {"rank", "congestion", "results"}),
    results = Table.TransformColumns(#"Expanded Record", 
        {
            {"results",
            each
                [
                    worst = [worstDay],
                    best = [bestDay],
                    res = [
                            #"worst Day/Month" = Text.From(worst[day])&"/"&Text.From(worst[month]),
                            #"worst Congestion" = Number.Round(worst[congestion]),
                            #"best Day/Month" = Text.From(best[day])&"/"&Text.From(best[month]),
                            #"best Congestion" = Number.Round(best[congestion])
                    ]
                ][res]
            }
        }
    ),
    #"Expanded results" = Table.ExpandRecordColumn(results, "results", {"worst Day/Month", "worst Congestion", "best Day/Month", "best Congestion"}, {"worst Day/Month", "worst Congestion", "best Day/Month", "best Congestion"})
in
    #"Expanded results"

         

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    John343467 unpivoting was the critical step in this to bring the years data on rows and then it is just a matter of getting the column values from the JSON which is pretty straightforward.