Expanding Json list

[John343467]

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. 

[parry2k]

@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.

[parry2k]

@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.

[John343467]

@parry2k Thanks. I'm looking for the following columns. 

City,Year,Congestion,Rank,worstDay/month,bestDay/month,worstDay Congestion,Best Day Congestion

The result for Dublin (https://www.tomtom.com/en_gb/traffic-index/page-data/dublin-traffic/page-data.json) would then be as below. Thanks again. 🙂

[CNENFRNL · Accepted solution]

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"

[John343467]

@CNENFRNL Great, thank you. Just wondering how you worked that out? I was stuck on it for quite a while. Any good sources I could investigate? Thanks again. 

[CNENFRNL]

Hi, @John343467 , glad that the solution is of help.

When I began to learn M language, a guru suggested me spending time to gain a firm grasp of the very fundamental data containers in M, namely Record, List, Table. So I spent quite some hours gaining acquaintance of transformations among them by using functions, such as Table.FromColumns, Table.FromRows, Table.FromRecords, Record.ToList, etc. It seems now such efforts pay off for me. You might want to give it a shot.

Enjoy M language, enjoy Power Query!

[amitchandak]

@John343467 , have you tried these options ?

https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...