Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jaap_Hijmans
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
mahoneypat
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors