Pagination and Dynamic Query for D365 OData Entities in Power BI 

Part 1: The Problem

What did not work for me with Chris's guide to dynamic query is the handling of query line itself. Let me explain:

https://{my company name masked}dynamics.com/data/DOT_GeneralJournalAccountEntries?$filter=DOT_MainAccount eq '1139030000'&cross-company=true 

In the above URI, following the guide I can make reference to the DOT_MainAccount dynamic, so instead of hardcoding it to '1139030000', I can refer to an Allias which in turn refers to a parameter or other table value (yes, you can refer cell level value from another table). But, what if you have a list of accounts?

Let's assume we have a List Parameter (it can be a table as well).

There is no IN statement in OData. The list above has three accounts and assuming it may change, we want a dynamic $filter query as below:

$filter=DOT_MainAccount eq '1139010000' or DOT_MainAccount eq '1139020000' or DOT_MainAccount eq '1139030000'

And in case the list has only top two accounts, you want to have the below query parameter dynamically:
$filter=DOT_MainAccount eq '1139010000' or DOT_MainAccount eq '1139020000'

The Web.Contents() connector utilizing Relative Path works very well and Chris documented the approach very clearly. For me though it was half the solution, as Web.Contents() connector has 10000 record limitation for D365 OData queries. To address this problem, I had to create pagination and call the connector in recursion until all records are obtained.

Setting Up Parameters

For pagination, you only need to make $skip query dynamic and row-based. D365 entity, the $filter query and Cross-company filter would be Explicit parameters, i.e. you don't need to create row level reference for those parameters since all pages will query same entity with same filter. 

I created the four parameters and set up example values:
📃EnityName (Type Text): "CustomersV3" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]
📃filterQuery (Type Text):  "$select=dataAreaId,CustomerAccount &$filter=PartyType eq 'Organization'" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]
📃CrossCompany (Type Text):  "true" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]
📃PageControl (Type Text): "Page 1 Only" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text, List = {"Page 1 Only", "All Pages"}, DefaultValue = "Page 1 Only"]

I will explain these parameters as I go on.

Get the total record counts

The first thing to do would be to get the total record count. Since this value would dictate how many times I will need to call the connector. I did this simply by adding $count to the original query and transforming the result to a table value with one column and one row.

let

  Source = Table.FromColumns(

        {Lines.FromBinary(

            Web.Contents(

                "https://quraz.operations.dynamics.com/data/",

                [RelativePath = EntityName&"/$count?"& filterQuery &"&cross-company="& CrossCompany]), null, null, 932)}),

  #"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "TotalRecords"}}),

  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"TotalRecords", type number}}),

  #"Added Custom" = Table.AddColumn(#"Changed Type", "TotalpageNo", each Number.RoundUp([TotalRecords]/10000)),

  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"TotalRecords"}),

  #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns", {{"TotalpageNo", Int64.Type}})

in

  #"Changed Type1"

In the above query, we use relative path to pass EntityName, filterQuery and cross-company values in conjuction with $count query to get total number of records. EntityName is the name of the table we want to query. filterQuery contains the filter you want to apply.
In this example, filetrQuery = $select=dataAreaId,CustomerAccount &$filter=PartyType eq 'Organization', which is not very Dynamic. However, if you have a table that formats your requirement to a single line query like above, you can make reference to that value as well. Consider the following case:

You have a table name 'QueryGenerator' that pre-formats your filterQuery based on some dynamic data source:

let

  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkksSXQsSk30TFHSUQpILCqpDKksSFVILVRQ9y9KT8zLrEosyczPU1eK1YlWci4tLsnPTS1yTE7OL80rAepQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [select = _t, filter = _t]),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"select", type text}, {"filter", type text}}),

  #"Replaced value" = Table.ReplaceValue(#"Changed column type", "", null, Replacer.ReplaceValue, {"filter"}),

  #"Filled down" = Table.FillDown(#"Replaced value", {"filter"}),

  #"Grouped rows" = Table.Group(#"Filled down", {"filter"}, {{"select", each Text.Combine([select],", "), type nullable text}}),

  #"Reordered columns" = Table.ReorderColumns(#"Grouped rows", {"select", "filter"}),

  #"Added prefix" = Table.TransformColumns(#"Reordered columns", {{"select", each "$select=" & _, type text}}),

  #"Added prefix 1" = Table.TransformColumns(#"Added prefix", {{"filter", each "$filter=" & _, type text}}),

  #"Merged columns" = Table.CombineColumns(#"Added prefix 1", {"select", "filter"}, Combiner.CombineTextByDelimiter("&", QuoteStyle.None), "filterQuery")

in

  #"Merged columns"

if you want to reference the value above, your TotalPageCount query would look something like below:

let

  Source = Table.FromColumns(

        {Lines.FromBinary(

            Web.Contents(

                "https://quraz.operations.dynamics.com/data/",

                [RelativePath = EntityName&"/$count?"& QueryGenerator{0}[filterQuery] &"&cross-company="& CrossCompany]), null, null, 932)}),

  #"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "TotalRecords"}}),

  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"TotalRecords", type number}}),

  #"Added Custom" = Table.AddColumn(#"Changed Type", "TotalpageNo", each Number.RoundUp([TotalRecords]/10000)),

  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"TotalRecords"}),

  #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns", {{"TotalpageNo", Int64.Type}})

in

  #"Changed Type1"

{0} gives you first row access and [filterQuery] gives you column access. When used in conjunction, you can refer to the first-row value.

Whether you decide to use dynamic or static reference for your filter, the TotalPageCount will give you the number of pages you would have should each page contain 10000 records. If you look at the code above, you will see that I hard coded this since the value doesn't change. I then rounded it up to give me the total number of pages. The result is as follows:

We are almost there!!!

Query the whole dataset

The next part is the easiest. We generate a list based on the TotalpageNo. I added an if statement that gives me control of pageNo queried. I did this so that I can add necessary transformations before I query all the pages as that can be time consuming.

if PageControl= "Page 1 Only" then {1..1} else {1..TotalPageCount{0}[TotalpageNo]}

As you can see, should I release the PageControl parameter, it will generate a list from 1 to TotalpageNo. After some basic transformation, I added the skip column which determines how many records from the top I should skip. This should be 0 for first page and then be chronological multiples of 10000 for subsequent pages:

 Table.AddColumn(#"Changed Type", "skip", each ([PageNo]-1)*10000)

I then added top, which is always 10000 and is actually unnecessary since the query will automatically restrict the result set to top 10000 results only. I decided to keep this anyway for future proofing.

Table.AddColumn(#"skip type text", "top", each 10000)

The last part is querying the OData entity using Web.API connector.

Table.AddColumn(#"top type text", "Custom", each Json.Document(

        Web.Contents(

            "https://quraz.operations.dynamics.com/data",

            [RelativePath = EntityName & "?" & filterQuery & "&cross-company=" & CrossCompany &"&$skip="&[skip]&"&$top="&[top]])))

By using skip and top, we can control pagination and query all pages. Below is the full Query for reference:

let

  Source = if PageControl= "Page 1 Only" then {1..1} else {1..TotalPageCount{0}[TotalpageNo]},

  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "PageNo"}}),

  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"PageNo", Int64.Type}}),

  #"Added skip" = Table.AddColumn(#"Changed Type", "skip", each ([PageNo]-1)*10000),

  #"skip type text" = Table.TransformColumnTypes(#"Added skip", {{"skip", type text}}),

  #"Added top" = Table.AddColumn(#"skip type text", "top", each 10000),

  #"top type text" = Table.TransformColumnTypes(#"Added top", {{"top", type text}}),

  #"Added Custom" = Table.AddColumn(#"top type text", "Custom", each Json.Document(

        Web.Contents(

            "https://quraz.operations.dynamics.com/data",

            [RelativePath = EntityName & "?" & filterQuery & "&cross-company=" & CrossCompany &"&$skip="&[skip]&"&$top="&[top]]))),

  #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"PageNo", "Custom"}),

  #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"value"}, {"value"}),

  #"Expanded value" = Table.ExpandListColumn(#"Expanded Custom", "value"),

in

  #"Expanded value"

The last step requires you to expand the value and make necessary transformations before you release PageControl parameter and close the dataflow.

This works, however, don't skip this last step:
Go to Options >> Privacy >> Check on 'Allow combinig data from multiple sources ....'