• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Slow Refresh With Background Preview Turned Off|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Slow Refresh With Background Preview Turned Off|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QuerySlow Refresh With Background Previe…
sp_PrintTopic sp_TopicIcon
Slow Refresh With Background Preview Turned Off
Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
1
May 16, 2020 - 3:17 am
sp_Permalink sp_Print

Hi Mynda,

Thank you for the tips to speedup refresh times, however, despite disabling the background data preview, I am unable to see much of an improvement in the refresh times. Moreover, the load to data model time is even horrible.

 

For your reference, I usually work with data that averages 5 million rows and my hardware configuration is as follows:

Processor - i5

RAM - 8GB

Do you think the processor and RAM could be the issue?

Any help would be appreciated

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 16, 2020 - 9:16 am
sp_Permalink sp_Print

Hi Salman,

Your RAM might have something to do with it, but it might also be the data source and your query structure. Perhaps you can provide some more information about your query or the M code.

Mynda

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
3
May 16, 2020 - 10:43 pm
sp_Permalink sp_Print

Hi Mynda,

Shared below is the M code for one of the many transformations I do in a single excel file.

Data source is a shared location on a network. The rows count is close to 5 million and the time it takes to load to data model is at least 35 mins.

 

let
Source = Folder.Files("\\ant.amazon.com\dept-as\BLR12\TRMS\BLR BRI\Salman\QBR\Raw Data\FCB\FCB$"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from FCB$", each #"Transform File from FCB$"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from FCB$"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from FCB$", Table.ColumnNames(#"Transform File from FCB$"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"FCB", type number}, {"Max Week", type text}, {"Rpt Week", Int64.Type}, {"Rpt Year", Int64.Type}, {"Goal * Vol", type number}, {"Goal", type number}, {"Achieved", type number}, {"Rpt Month", type date}, {"Temporal Dimension", type text}, {"SUM([Numerator])/ 10000", type number}, {"Category1", type text}, {"Function Name1", type text}, {"Marketplace Id1", type text}, {"Number of Records", Int64.Type}, {"Period Name Mth1", type date}, {"Proposed Goals", type number}, {"Sub Category1", type text}, {"Sub Metric Name1", type text}, {"Actual Denom", type number}, {"Attribute Id", type text}, {"Calendar Date", type date}, {"Category", type text}, {"Denominator", type number}, {"Function Name", type text}, {"Location Name", type text}, {"Login", type text}, {"Manager", type text}, {"Marketplace Id", type text}, {"Metric Name", type text}, {"Numerator", Int64.Type}, {"Outsource Flag", type text}, {"Period Name Mth", type date}, {"Rpt Period Name Week", type text}, {"Sub Category", type text}, {"Sub Metric Name", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Location Name", "Site"}}),
#"Inserted Month" = Table.AddColumn(#"Renamed Columns", "Month", each Date.Month([Calendar Date]), Int64.Type),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Month", "RoN", each if [Site] = "BLR" then "BLR" else if [Site] = "VIR" then "BLR" else "Rest of Network"),
#"Merged Queries" = Table.NestedJoin(#"Added Conditional Column",{"Category"},CatAlias,{"Category"},"CatAlias",JoinKind.LeftOuter),
#"Expanded CatAlias" = Table.ExpandTableColumn(#"Merged Queries", "CatAlias", {"Alias"}, {"Alias"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded CatAlias",{{"Alias", "Cat Final"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",12,1,Replacer.ReplaceValue,{"Month"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",5,4,Replacer.ReplaceValue,{"Month"})
in
#"Replaced Value1"

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
May 18, 2020 - 1:25 pm
sp_Permalink sp_Print

Hi Salman,

Background data preview might help only when you are in power query editor, that's the place where the preview is used. In a normal refresh there is no preview.

With large data, you have to optimize the process as much as possible.

Merging queries might not be a good idea, because it makes no sense at this point, I assume you will not load data to sheet.

Load data to power pivot, then create a relationship between those 2 tables, instead of merging them, the result will be the same but more efficient.

If not possible, try adding Table.Buffer before merge.

You are using default combiner, which is not usually flexible, you should build your own functions to combine files and sheets, see an example here: https://www.myonlinetraininghu.....npivotting

Also, if you have data that does not change (historical data), might be a good idea to convert it once and store the converted data, next time load the converted data instead of processing same data each time, will be faster to load.

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
5
May 19, 2020 - 12:58 am
sp_Permalink sp_Print

Hi Catalin,

Thank you so much for the reply.

 

- Now, I have an even more clear picture about the background data preview.

- With respect to merging queries, what you said actually makes sense, will try building relationship between the two tables.

- Could you please show me the exact place and syntax to add the Table.Buffer

- I am quite new to power query, so at this time, building my own functions to combine files would a challenge.

- Right now, there's no historical data. Will keep your suggestion in mind.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
May 19, 2020 - 10:52 am
sp_Permalink sp_Print

Hi Salman,

Where to place Table.Buffer.

Mynda

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
7
May 20, 2020 - 9:29 pm
sp_Permalink sp_Print

Hi Mynda,

Thanks for the reply.

This almost clears the confusion that I have about the Table.Buffer except for one thing - In the post "Where to place Table.Buffer", you had asked to wrap it around the Source (which is ideally the first step in power query) whereas Catalin (reply# 4) had suggested to add Table.Buffer before merge. Let's say I'm doing a merge operation at step #10, does it mean I need to add Table.Buffer at step 9?

 

Besides, I really appreciate Catalin's suggestion on building relationship between the two tables instead of merging them. I'm able to save quite some time following his advice. However, I have a follow up question to what Catalin suggested - if I need to use the merged column for some operation in the source data, then just building a relationship won't help is what I think, you actually need to do the merge so that you have the column in the source data. Please correct me if I'm wrong.

 

Thanks

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
May 21, 2020 - 12:30 am
sp_Permalink sp_Print sp_EditHistory

Hi Salman,

Try both places (Source, step 9), see which one is faster. Buffering is a trial-error process, not a fail-proof recipe, sometimes works, sometimes don't.

About merging or using relationships:

it really depends on what you need to do. Best is to reconsider the operations needed, if some of them can be done in power pivot without merging tables in power query, will help you speed up the process.

Most operations can have more than one solution, if you can reconsider that merge operation you might be able to find a solution without merging.

Avatar
Salman Baig
Member
Members
Level 0
Forum Posts: 10
Member Since:
May 16, 2020
sp_UserOfflineSmall Offline
9
May 22, 2020 - 5:12 am
sp_Permalink sp_Print

Hi Catalin,

Thank you for the clarification. Will consider both options and see what works for me.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, baber Tufail
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.