Forum Discussion

Jaap_Hijmans's avatar
Jaap_Hijmans
New Member
5 years ago

Power Query - select smaller dataset in PBI

Hello forum,

I created a datastream in which I extract the complete General Ledger Entrys from Dynamics NAV. And that's a huge table.
In my PBI report I only need the most recent years.

In my PowerBI report I have the data imported with the query below  

let
Bron = PowerBI.Dataflows(null),
#"21de1ca4-5bcf-4065-b258-be3d3d40c437" = Bron{[workspaceId="21de1ca4-5bcf-4065-b258-be3d3d40c437"]}[Data],
#"408be3e3-b7e0-45b8-95f9-0773faac0351" = #"21de1ca4-5bcf-4065-b258-be3d3d40c437"{[dataflowId="408be3e3-b7e0-45b8-95f9-0773faac0351"]}[Data],
#"Vogelbescherming$G_L Entry1" = #"408be3e3-b7e0-45b8-95f9-0773faac0351"{[entity="Vogelbescherming$G_L Entry"]}[Data]
in
#"Vogelbescherming$G_L Entry1"

 

Can I add code to this query to import only for example the last 5 years? If yes, what code do I have to add??

Or do I have to change the query in the Datastream definition below?  

 
Bron{[Schema = "dbo", Item = "Vogelbescherming$G_L Entry"]}[Data]

where 'Bron' = Source in english 

 

I appreciate your help. Any advise or link to other topics on this issue are welcome.

Hope this post wil bring me a solution.


Jaap Hijmans

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Since Dataflows do not use "query folding", you can add a step to filter your data to the last five years but when you load the data, it will first load all the data.  A frustrating limitation of Dataflows.  If there is a way to have folding, I'd like to know it too.

     

    One option would be to make a filtered dataflow or dataset from that dataflow, and use either as the source (so that the long refresh time happens on the cloud).

     

    Regards,

    Pat