Import data from Excel dynamic named range

[webportal]

Hello,

I'd like to build a dashboard in Power BI desktop using a dnynamic named range from Excel.

That's is, the name of the range is fixed but the range it refers to is managed in Excel VBA.

This doesn't seem to be working. When the data is refreshed in Excel, I click the refresh button in Power BI but nothing changes.

Can anyone help?

Thanks!

[ImkeF]

The way to go would be to replicate the dynamic logic in the query editor using M language.

So if you would provide descriptions/examples I could help you to set this up.

[webportal]

Thank you for your help.

Say, I have a named rage ('myRange') that refers to:

• Sheet1!A1:C5 if the user selects this option
• Sheet2!A1:C5 if the user selects this option
• Sheet3!A1:C5 if the user selects this option

In Excel, this works fine. I'm using VBA to dynamically change the range reference. In Power BI, I import 'myRange' and it imports the data correclty.

The problem is that the data isn't refreshed accordingly when the user changes the range reference in Excel,

[ImkeF]

For example you could move the process of selecting an option from Excel to Power BI by using a parameter list there. Or is the selection of the dynamic range done by a different user?

Just create a table with the option names in one column and the ranges on the sheets in the second column. Then create a parameter which acts as a filter on the options-column of this table and use the returned value from the 2nd (ranges-) column as a parameter/variable for your import-query.

Hope this makes sense?

[webportal]

Hello,

Sorry for the late reply.

The user will select which area to use in Excel, it's not possible to shift that to Power BI.

Is there a way to update the range source in Power BI?

Thanks for your help.

[ImkeF]

Did you save the xls before trying to refresh from PBI?

[webportal]

Yep! 

[ImkeF]

Sorry but I have no further idea why this doesnt work then.

[webportal]

No problem, thanks anyway!

Nice blog, by the way, I'm also researching on controlling/financial analysis with Power BI.

[ImkeF]

Thank you 🙂

I must admit that I find it hard to understand that the range you're successfully importing doesn't reflect the change.

I know cases where named ranges are not visible to the query editor/Power Query  (Pivot ranges and cubefunctions sometimes). But once an object has been imported it should reflect the change. I'd probably send a frown then because that would count as a bug in my eyes.