Lookupvalue in excel and uppdating in PowerBI

[mohsin-raza]

I have excel workbook having lookupvalues from another workbook.

It takes lot of time in refreshing.

how can we minimized this effect during uppdating in powerbi especialy when file is store in Sharepoint? 

[Anonymous]

Hi @mohsin-raza ,

There‘s an article about optimized refresh

Speed Up Data Refresh Performance In Power BI Desktop Using Table.View

Best Regards,

Stephen Tao

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

[edhans · Accepted solution]

@mohsin-raza - you are going to have to be more specific in what you are referring to here. LOOKUPVALUES() in Power BI cannot look up data in a workbook, only a table that is loaded in the model. LOOKUPVALUES will not impact the refresh unless you used it in a calculated column, and you should avoid them. Do a Merge in Power Query instead. Don't treat Power BI like it is Excel. It isn't, and doing an equivalent to a VLOOKUP will not perform well. The worst thing you can do for your models is treat that data grid in Power BI like a spreadsheet. It most definitely is not that.

For merging in Power Query, see this article. That is for Excel's Power Query, but Power BI is the same.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns