Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
@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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.