Combine Key/Value Lists from Web Response

[Anonymous]

Hi

I am trying to get entity record counds from dynamics using its API and the function RetrieveTotalRecordCount.

I am making a call similar to this

https://foobar.crm.dynamics.com/api/data/v9.1/RetrieveTotalRecordCount(EntityNames='salesorder','sal...

which returns data as follows

{

  "@odata.context": "https://foobar.crm.dynamics.com/api/data/v9.1/$metadata#Microsoft.Dynamics.CRM.RetrieveTotalRecordCo...",

  "EntityRecordCountCollection": {

    "Count": 4,

    "IsReadOnly": false,

    "Keys": [

      "salesorder",

      "salesorderdetail",

      "invoice",

      "invoicedetail"

    ],

    "Values": [

      6679,

      10547,

      17568,

      59618

    ]

  }

}

What i would like is a table with the table name (key) and the correcpoding rowcount(value)  ie

Table Name	Row Count
salesorder	6679
salesorderdetail	10547
invoice	17568
invoicedetail	59618

As you can see the response returns 2 key/value lists. 

however the only way i can find to do this currently is to execute the query twice and expand each of the lists separately into a table adding an index field to each then joining the 2 tables back up together on this index column.  This seems a rather clunky way of doing to so was wondering if anyone could suggest a smarter way?

thanks in advince

[Smauro · Accepted solution]

@Anonymous
You could try something like

    Json = Json.Document(Response)[EntityRecordCountCollection],
    toTable = Table.FromList(List.Zip({Json[Keys], Json[Values]}),(x) => {x{0}, x{1}}, {"Table Name", "Row Count"}),
    tableType = type table [ #"Table Name" = Text.Type, #"Row Count" = Int64.Type],
    Final = Value.ReplaceType ( toTable, tableType)

where Response is the response.

Cheers

[Anonymous]

Thank you @Smauro 

With your example i was able to get it working with the following code

let
    Source = OData.Feed("https://xxxxxx.crm11.dynamics.com/api/data/v9.1/RetrieveTotalRecordCount(EntityNames=['contact','lead','serviceappointment','task'])", null, [Implementation="2.0"]),
    EntityRecordCountCollection = Source[EntityRecordCountCollection],
    toTable = Table.FromList(List.Zip({EntityRecordCountCollection[Keys], EntityRecordCountCollection[Values]}),(x) => {x{0}, x{1}}, {"Table Name", "Row Count"})
in
    toTable