cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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

1 REPLY 1
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors