Forum Discussion

jambrose's avatar
jambrose
Frequent Visitor
9 years ago

Improving Summarize Efficiency

I'm trying to pull information from a large (~20 mil records, 48 colmuns) table, and I have a working method, but it's on the verge of what my machine can handle, and so I'm worried it won't scale up...
Need Help
jambrose's avatar
jambrose
Frequent Visitor
9 years ago

I'd be open to trying it that way, but I haven't so far been able to find a way to do the same process (pull a top value based on sorting 3 other columns) on the query end of things.  Any ideas?

jambrose's avatar
jambrose
Frequent Visitor
9 years ago

Well, I gave the query side a shot, and was able to accomplish the same task with the following code:

= Table.Group(#"Big_Table", {"ID"}, 
   {{"First Value", 
      each Table.First(Table.Sort(_, 
          {{"Sort1", Order.Ascending}, {"Sort2", Order.Ascending}, {"Sort3", Order.Ascending}})
      )[Value] }})

However, this still runs into memory load errors and fails to run on the full data set.  Not sure what else to try here.  Unless anyone else has ideas, I think I'll try moving to R for the processing steps and then import back to Power BI to generate reports.

 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi jambrose,

    You use the SSMS connect your Power BI model. Then profilie Power BI Desktop Model using SQL Server Profiler using the following steps. More details, please review this article.

    Thanks,
    Angelia