Forum Discussion

jpalaci1's avatar
jpalaci1
Helper I
4 years ago
Solved

Excel Power Query Data Model to connect to PBI and to different Excel

I have my raw data and lookup tables in seperate Excel files. I wish to create a dashboard on that data for users of the data while allowing my team to be able to use that same data in Excel for anal...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi jpalaci1 ,

     

    I would do it like this:

     

    Bring all Excel data sources into Power BI desktop and create the data model.

    Create your measures/dashboard/visualisations/report in the same report and publish to the PBI Service.

    Find the dataset within the workspace that you published to and select 'Analyze in Excel'.

    This will create an Excel workbook with a live connection to the PBI dataset (so it's always as up-to-date as the PBI version) with all the relationships and measures intact that your analysts can use.

     

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi jpalaci1 ,

 

I would do it like this:

 

Bring all Excel data sources into Power BI desktop and create the data model.

Create your measures/dashboard/visualisations/report in the same report and publish to the PBI Service.

Find the dataset within the workspace that you published to and select 'Analyze in Excel'.

This will create an Excel workbook with a live connection to the PBI dataset (so it's always as up-to-date as the PBI version) with all the relationships and measures intact that your analysts can use.

 

 

Pete

  • jpalaci1's avatar
    jpalaci1
    Helper I
    4 years ago

    Thank you! I will do this in future models.