Filter data from one table and store it in another table

[Aman-K]

Hi All,

I have a query in Power BI which contains all data from source. I need to filter the query where IssueType="Initiatives" and save it down as a new query . Can anyone please let me know how this can be acheived?

Many Thanks,

Aman

[TomMartens]

Hey @Aman-K ,

please be aware that Power Query is not a data store, and yes - it can load/transform huge amounts of data. The Power BI dataset stores the data.

Just click on the little box in the status bar of a query and switch to "... entire dataset" 

I'm wondeirng if you are aware of this site, it is offering a wealth f learning material: https://learn.microsoft.com/en-us/training/powerplatform/power-bi?WT.mc_id=powerbi_landingpage-marke...

Next to that I also recommend reading the article:  Planning a Power BI Enterprise Deployment - https://learn.microsoft.com/en-us/power-bi/guidance/whitepapers 

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Aman-K]

Thanks for this @TomMartens . When I change the maxResults limit to 2000 in my query in advanced editor then I get the below error message :

This is my code in advanced editor taking data from Jira using Rest API. I am unable to get more than 1000 records . I need all the records for the reports so i'm really stuck .

let
Source = Json.Document(Web.Contents("https://jira.boigroup.net/rest/api/2/search?jql=project%20in%20(ddaseau,ddasdws,ddashd,ddasdux)&star...", [Headers=[Authorization="Bearer Nzk5MTE4NDU3NTM5Ovc2WYFJpdc9acLGmDGxhYu1H8Is"]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded issues" = Table.ExpandListColumn(#"Converted to Table", "issues"),
#"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"issues.expand", "issues.id", "issues.self", "issues.key", "issues.fields"}),

Thanks a lot for your help so far .

[TomMartens]

Hey @Aman-K ,

the most simple thing is to create a new query with reference to the first query: Referencing Power Query queries - Power BI | Microsoft Learn
Create the filter in the second query as you described above - done!

A more advanced technique that comes with some performance benefits (at least from observations) is been mentioned by @ImkeF : https://www.thebiccountant.com/2023/02/07/reference-intermediate-step-from-a-different-query-in-powe...

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Aman-K]

Hi @TomMartens 

If I add a filter in the query as you mentioned then the query will still have all the data in it , it will just show filtered records. I am looking for a way so the query only has the filtered records and not any other records. Hope it makes sense. Thanks 

[TomMartens]

Hey @Aman-K ,

this is not true, a filter inside a Power Query query filters the data, data that is not passing the constraint of the applied filter will not be part of the table in the Power BI dataset.

Regards,

Tom

[Aman-K]

That's great. Thanks Tom. Can you please let me know if Power query can have more than 1000 rows? I have around 1500 records in Jira but the query is only showing 1000 , I tried to create 2 queries (one has data upto 1000 rows and the other one from 1001 onwards ). How can I merge data from 2 queries in the report ? If I use merge queries option then it still doesn't store more than 1000 rows. Any quick way to acheive this please? 

Thanks