Log In
Register
Home
June 24, 2020
Offline
Hello friends Hope all is safe & Well!
I have a PowerPivot that reads from a PowerQuery. The PQ combines many files, so I have more than 30 million rows.
I get requests to display results of 5-10 PatientIDs, from millions of rows. I don't have a problem with that. I do it with DAX, but the problem is it takes a long time to refresh, to apply the Lookupvalue on all the millions of rows. Here is my wish instead.
Can you please help me filter the slicer of PatientID, or Filter the PivotTable (Pic#1), based on the values in sheet DB; range B (Pic#2).
July 16, 2010
Offline
Hi Nawaf,
Welcome to our forum!
When you filter the PivotTable using the Slicer it is not refreshing the query. To do that would take even longer! You should only refresh the query when you expect there is new data to get from the source. Once you have the data loaded into Power Pivot you don't want to be refreshing the query.
If the Slicer is slow to respond it's due to the amount of data in your file and possibly inefficient measures you have in the PivotTable. That said, the measure shown in the screenshot looks like an implicit measure, so it won't be that causing the problem. Of course I'm assuming there aren't other inefficient measures in other PivotTables.
You also need 64-bit Excel to run Power Pivot. I wonder if you only have 32-bit? You can check on the File tab > Account > About Excel.
Mynda
June 24, 2020
Offline
Thank you very much Mynda, it is my pleasure to be here.
Sorry for not replying any sooner; was trying different methods before replying. Anyway here is my responses to your post.
You should only refresh the query when you expect there is new data to get from the source.
Great, is there a way to refresh only the new data instead of refreshing all the past and new?
Once you have the data loaded into Power Pivot you don't want to be refreshing the query.
I am forced to refresh, because I am choosing certain account numbers (say 170 accounts out of a million rows). Filtering manually would be a painful exercise. Refreshing would apply the dax to do Lookup function and filter the required data.
You also need 64-bit Excel to run Power Pivot.
I have 64, and 16RAM computer. It would be awesome, if there is anyway to speedup the process of refreshing, at least refreshing the latest added data.
Thank you So much!
July 16, 2010
Offline
Hi Nawaf,
There's no way to do an incremental refresh in Power Query in Excel. It is available in Power BI though: Power BI Incremental Refresh.
To filter queries based on a list of accounts you need to first create a query containing a list.
Then you can use the List.Contains function in the Fact Table query to filter the rows, e.g.:
= Table.SelectRows(#"Name of your last step", each List.Contains(AccountNumberTable , [AccountNumberColumn]))
It's a bit complex to describe here, but if you get stuck, please come back with a small sample file containing an extract of the fact table data and list of accounts and your query attempt and we'll help you further.
This post has some tips for speeding up queries.
Mynda
1 Guest(s)
