Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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/v...
Need Help
  • Smauro's avatar
    Smauro
    5 years ago

    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

Smauro's avatar
Smauro
Solution Sage
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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