Forum Discussion
Helper IIIFilter data from one table and store it in another table
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
6 Replies
- TomMartens
Super User
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-marketing-page
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-KThanks 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)&startAt=0&maxResults=1000", [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-power-query/
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
