Forum Discussion

webportal's avatar
webportal
Impactful Individual
9 years ago

Import data from Excel dynamic named range

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!

Need Help

9 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    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's avatar
      webportal
      Impactful Individual
      9 years ago

      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's avatar
        ImkeF
        Community Champion
        9 years ago

        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?