Stuck with "nested" records

[SimonKragh]

Hi,

I have been trying to develop an interactive dashboard to visualise performance for overwatch players, however i have gone stuck.

Firstly i created a hero dimension with the basical stats in form of playtime etc using the following api call:

Json.Document(Web.Contents("https://api.lootbox.eu/pc/eu/donatellow-2669/competitive-play/heroes"))

Afterwards i wanted to get performance by each individual hero by using the following API call (as illustrated in this article: http://datachix.com/2014/05/22/power-query-functions-some-scenarios/😞

Json.Document(Web.Contents("https://api.lootbox.eu/pc/eu/"& accountName &"/competitive-play/hero/"& charString & "/"))

Where accountName and charString is identifier for the account and which character. However, this returns the following result: 

I have tried using the expand button which leads to the following result, which is not what i want:

Each record contains the following columns which i want to store directly in my table:

Any suggestions to how i achieve this? 

[ankitpatira]

@SimonKragh I think it will work if you do it as two seperate data queries. So have another query with all your Json records. Then pivot it so columns (Roadhog, Lucio, Tracer) becomes rows in a new column (call it Character Name). Then Merge both queries using Character name column. Hope that make sense. 

[SimonKragh]

@ankitpatira I tried your suggesting but it leads me to the same result within its own Query this time as seen below. 

Which is quiet the same except i dont have the first columns with playtme etc.

Any other suggestions? 

Regards.

[ankitpatira]

@SimonKragh Can you try Append instead of Merging. I think append will do the trick.

[SimonKragh]

Hi.

Nope, didn't do the trick either 😕 

Isn't there someway in my functions that i can get the nested record directly to the column? 

let getOWHeroesStats = (charString as text) =>
  let Source = Json.Document(Web.Contents("https://api.lootbox.eu/pc/eu/donatellow-2669/competitive-play/hero/"& charString & "/"))
in Source
    in getOWHeroesStats

The appove code gives me the record which contains a record. Do you have any suggestiosn how i can access the nested record directly from the above query?

Best,

Simon

[v-ljerr-msft · Accepted solution]

@SimonKragh

---

The appove code gives me the record which contains a record. Do you have any suggestiosn how i can access the nested record directly from the above query?

---

Please try the formula below.

let getOWHeroesStats = (charString as text) =>
 let Source = Json.Document(Web.Contents("https://api.lootbox.eu/pc/eu/donatellow-2669/competitive-play/hero/"& charString & "/")),
 Hero = Record.FieldOrDefault(Source, charString),
 #"Converted to Table" = Record.ToTable(Hero),
 #"Transposed Table" = Table.Transpose(#"Converted to Table"),
 #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")

in #"Promoted Headers"
 in getOWHeroesStats

Following is the result for hero "Tracer".

Regards

[SimonKragh]

@v-ljerr-msft Yes yes yes! Works like a charm! Thank you so much!