PowerBi DataFlows–Filtering by Date

The cool feature of incremental refresh is only available to PowerBi Premium instances. So, what do the poor folk do? Well, we fake it!

The following is based on Dynamics CRM data, which provides the modifiedon field for all entities, but it can be extended to use any date field. You simply use the Table.SelectRows function to filter based on the ModifiedOn field. (the example shows how I did it on the “SystemUser” entity).

let
   Source = Cds.Entities(https://xxxxx.crm.dynamics.com, null),
   #"Navigation 1" = Source{[Group = "entities"]}[Data],
   #"Navigation 2" = #"Navigation 1"{[EntitySetName = "systemusers"]}[Data],
  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 2", {{"modifiedon", type datetime}}),
   #"Filtered By Date" = Table.SelectRows(#"Changed column type", each DateTime.From([modifiedon]) >= DateTime.From("2018-01-01"))
in
   #"Filtered By Date"