Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Combine Key/Value Lists from Web Response

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

 

{
  "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 NameRow Count
salesorder6679
salesorderdetail10547
invoice17568
invoicedetail59618

 

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

 
1 ACCEPTED SOLUTION
Smauro
Solution Sage
Solution Sage

@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




Feel free to connect with me:
LinkedIn

View solution in original post

2 REPLIES 2
Smauro
Solution Sage
Solution Sage

@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




Feel free to connect with me:
LinkedIn

Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors