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

[jpalaci1]

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 analysis.

I was thinking of using Power Query in Book1 to pull all data in, all cleansing of data, create the data model, and have this workbook being the "work horse" of the data for ETL.

I then have Power BI connect to Book1 to make my visuals and then Excel to connect to Book1 so my team can still do analysis off this data. 

Am I doing this right? I thought about using Access but the limit of data scares me as a database I made to warehouse data is getting ot the 2GB limit. 

[BA_Pete · Accepted solution]

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]

Thank you! I will do this in future models.