Most of you familiar with accessing excel files from Power BI Desktop. Let me as a below question.
Will Power BI recognize “Defined Names” from Excel?
If you take excel expert who use most of the features from excel for analysis, they may create multiple sheets, tables (range of cells) & defined names (range of cells). They may be interested to see all the objects in Power BI. Will Power BI help excel experts to access all the objects?
The above two questions are same but slightly different in context. Yes, is the answer for above two questions.
Let me explain Define Name in Excel first.
Like table, we can define a name for range of cells. It is kind of subset of data from the whole sheet data.
You can find the “Define Name” under Formulas tab.
Select the Define Name, it will ask you to enter the name and range of cells in refer section as like below.
I have defined a name called “Profit” with L column. This will be smaller table with Profit as a column within the table.
You can define multiple names, and all can be referred in your excel calculations. You can check the list of names you defined as like below.
Now, you got a basic idea of Define Name in excel. Refer excel articles to understand more about this feature.
Let us come to our Question again. I said Yes, Power BI will recognize these names. Let me show you.
If I connect this excel in Power BI Desktop, you can below objects under Navigation.
In the above screenshot, Country, Sale_Price & Sales are defined names.
All the above defined names are recognized as tables in Power BI. We can use them for report.
You may get the following question; will it be any performance issue when we use these defined names?
It depends on the scenario. Consider you have a excel with more number of columns and you have defined a name with few columns and measures. In this case, you can consider only the defined name and no need to use the full sheet in Power BI. This will improve the performance.
Try to avoid using the whole sheet and defined names together because defined name is already available in whole sheet and Power BI refresh will take time to refresh as it needs to refresh the whole sheet and defined name.