Forum Discussion

ns29's avatar
ns29
Helper I
6 years ago

Suppress refresh in Power Query when not necessary

So I have this situation, please see code snippet below:

  • ODBC query pulling 6M rows into Power Query raw_data table, about 5 mins (once pulled in, I don't need to refresh ODBC data to conduct analysis)
  • Create a reference to the raw_data table and add a custom column decay
  • decay is basically a simple math function of a column on the table called age and a static numeric parameter called b_value 
  • Every time I change the parameter b_value, it proceeds to refresh the ODBC data which is absolutely not optimal
  • The only data that is getting loaded into the data model is a highly condensed version of the data, grouped by the age field and showing the sum of the decay, ~100 rows

How do I prevent this refresh of the large ODBC data set?

 

I have already tried toggling the Background Data and Parallel Loading options with no success. 

 

Thanks in advance!

 

let
    Source = raw_data,
    #"Added Custom" = Table.AddColumn(Source, "decay", each Number.Power([age],b_value))
in
    #"Added Custom"

 

 

Need Help
Tips and Tricks

3 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi ns29 , 

    It seems that you want to refresh partial data, right? I think you might could try the incremental refresh, you could service-premium-incremental-refresh  for details. But you need to pay attention, incremental refresh also has some limits, you could refer to above link.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ns29's avatar
      ns29
      Helper I
      6 years ago

      Hi dax, I'm not looking to refresh any data in a single session of analysis once the data has been pulled into the file. Incremental refresh would not work in my case unfortunately as it asks for parameters on the data set itself where I have none set. 

       

      I know I'm doing something wrong, hard to believe the engine isn't smart enough to know when to not fetch data. Hopefully someone else can chime in here.

       

      Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hey there. If you are using Excel for this, you could load the table to a pivot table, then change the measure or columns in the pivot table into formulas using the OLAP options in the Analyze ribbon. Then, you could just type your parameter in any blank cell, and then a formula like = PivotCellName * ParameterCell. Change the parameters as you like, the data model is already loaded!