Forum Discussion

SimonKragh's avatar
SimonKragh
Frequent Visitor
10 years ago
Solved

Stuck with "nested" records

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...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    10 years ago

    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

ankitpatira's avatar
ankitpatira
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
    SimonKragh
    Frequent Visitor
    10 years ago

    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's avatar
      ankitpatira
      Icon for Community Champion rankCommunity Champion
      10 years ago

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

      • SimonKragh's avatar
        SimonKragh
        Frequent Visitor
        10 years ago

        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