Forum Discussion
Helper IHow to refresh and load new Data in Model with applied Steps?
Dear community,
I hope somebody can help, as I have something really odd, which is against everything I can find in the web and I do not know why.
I have setup a rather complex PowerBI Model with a lot of tables. In the PowerQuery Manager I have applied several steps on each table (like replacing values, Filter, Keep TopN (sure to have enough lines selected), Changing Formats, Added columns, removed duplicates etc.) Working in the Desktop version.
I made sure that these steps are applied to all rows at the bottom left corner, where neccesary. Though I always have to redo this - is there a trick on keeping the "Apply to All" premanently, rather than having to redo it everytime ?
When I refresh the Data (it's a manual refresh) in the Power Query Editor I do see the new Data records, even after all Applied steps.
However when I close and load the Power Query Editor, click refresh in the Model and go to the table view the new data records do not appear and are not in the model.
When I undo all applied steps > close and load > save > back to Power Query > redo all Applied Steps > close and load the data becomes avalaible in the model.
What am I missing? Somebody having an idea for the two issues:
1) New data not loading into the model, despite being available in the Power Query
2) Steps applied to all rows permanently and not switching back to Top 1000
Hi JKross,
Thank you for reaching out to Microsoft Fabric Community.
Thank you rajendraongole1 for the prompt response
Open Power BI Desktop > Click on File > Options and settings, then click on Options.
Go to Global > Data Load > Ensure that Background Data refresh is enabled.If you want to always limit the number of rows fetched for example, if you have a query that fetches more than 1000 rows, you can modify your query to limit the data.
In Power Query Editor, apply a filter step to only load the top 1000 rows of your data:
Go to Home > Transform Data to open Power Query Editor.
Select the table/query you want to adjust.
Use the Keep Top Rows option in the Reduce Rows section of the Home tab.
Set the value to 1000.
Apply and close to keep only the top 1000 rows in your model.If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
8 Replies
- rajendraongole1
Super User
Hi JKross - In Power Query, ensure that your table is enabled to load to the model as below:
Right-click on the table in the Queries Pane (left side).>> Make sure “Enable Load” is checked.
Next Go to Power Query Editor → View → Query Dependencies>>Check if there’s any step that filters out all rows by mistake.
Next query 2:
Under Global → Data Load, set:
"Allow Data Preview to Auto-detect Column Types and Relationships for Entire Dataset">> "Always Allow Data Preview to Load More than 1000 Rows"
How to prevent power query editor from reloading d... - Microsoft Fabric Community
Can we Stop PowerQuery to Refresh at every step? - Power Query - Enterprise DNA Forum
Solved: PowerBI doesn't load all rows on refresh - Microsoft Fabric Community
Hope the above details helps
JKrossHi rajendraongole1 ,
thank you for hinting so quickly. Got it - half!
Indeed there was a silly and useless filter in one table. Once deleted all Data was loaded correctly.
Only 2) I could not find. Power Query always switches back to TOP 1000. I am working with the GErman version, and I couldn't make out (nor google could) what the Option "Allow Data Preview to Auto-detect Column Types and Relationships for Entire Dataset" would be called in the German version.
Do you know? This is how it looks like:- rajendraongole1
Hi JKross - This is visible in your screenshot under "Typenerkennung" (Type Recognition).
-
Open Power Query Editor.
-
Click on "Ansicht" (View) in the top menu.
-
Locate "Spaltenprofilierung basierend auf" (Column profiling based on).
-
Change it from "Top 1000 Zeilen" (Top 1000 Rows) to "Gesamter Datensatz" (Entire Dataset).
This should prevent it from switching back to Top 1000.
Hope this helps.
-
- JKross
Hi rajendraongole1 ;
TRied it: here is how my view tab "Ansicht" looks like:The Option comes again only at the bottom left corner (like in any other tab view)
I changed again all relevant tables to All Data, but when I go back it's again Top 1000 only.
Any other idea, where I can find it? 
v-achippaCommunity Support
Hi JKross,
Thank you for reaching out to Microsoft Fabric Community.
Thank you rajendraongole1 for the prompt response
Open Power BI Desktop > Click on File > Options and settings, then click on Options.
Go to Global > Data Load > Ensure that Background Data refresh is enabled.If you want to always limit the number of rows fetched for example, if you have a query that fetches more than 1000 rows, you can modify your query to limit the data.
In Power Query Editor, apply a filter step to only load the top 1000 rows of your data:
Go to Home > Transform Data to open Power Query Editor.
Select the table/query you want to adjust.
Use the Keep Top Rows option in the Reduce Rows section of the Home tab.
Set the value to 1000.
Apply and close to keep only the top 1000 rows in your model.If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
v-echaithraHi JKross ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E. - v-echaithra
Hi JKross ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E. - v-echaithra
Hi JKross ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E.
