Posts

7 Ways to Integrate Excel with Power BI

Slides from my presentation “7 Ways to Integrate Excel with Power BI”.

Power BI is gaining a momentum but Excel still rules the corporate world. Fortunately, Power BI and Excel are not exclusive choices. Join me to learn how you can preserve your Excel investment in Power BI. I’ll start by explaining the value Power BI brings to different types of users in your organization. Then, I’ll discuss and demo seven options for integrating Excel with Power BI:

  • Import Excel files in Power BI Service
  • Build self-service data models from Excel data
  • Deploy Excel models to Power BI
  • Convert Excel models to Power BI
  • Connect to Excel reports
  • Analyze Power BI datasets in Excel
  • Add Excel reports to Power BI dashboards

Automating Excel to Power BI Publishing

Excel 2016 added a Publish to Power BI menu to let you export or connect Excel workbooks to Power BI. You can read more about this Excel feature here. One area where Excel is still ahead of Power BI Desktop is that is has an object model that lets you automate tasks with VBA. Unfortunately, Power BI Desktop doesn’t have an object model so you have to resort to unsupported ways (aka hacks) to automate tasks, such as refreshing and publishing to Power BI. I discussed some here.

I’ve noticed that Microsoft added not yet documented PublishToPBI method to Excel 2016. With it, refreshing the Excel data model and publishing it to Power BI Service takes two lines of code (you’d need more code to open the Excel workbook from an external application).

Sub Macro1()

ActiveWorkbook.Model.Refresh

ActiveWorkbook.PublishToPBI PublishType:=msoPBIExport, nameConflict:= msoPBIAbort, bstrGroupName:=”<Some Workspace>”

End Sub

Downloads

7 Ways to Integrate Excel with Power BI

Slides from my presentation “7 Ways to Integrate Excel with Power BI”.

Power BI is gaining a momentum but Excel still rules the corporate world. Fortunately, Power BI and Excel are not exclusive choices. Join me to learn how you can preserve your Excel investment in Power BI. I’ll start by explaining the value Power BI brings to different types of users in your organization. Then, I’ll discuss and demo seven options for integrating Excel with Power BI:

  • Import Excel files in Power BI Service
  • Build self-service data models from Excel data
  • Deploy Excel models to Power BI
  • Convert Excel models to Power BI
  • Connect to Excel reports
  • Analyze Power BI datasets in Excel
  • Add Excel reports to Power BI dashboards

Automating Excel to Power BI Publishing

Excel 2016 added a Publish to Power BI menu to let you export or connect Excel workbooks to Power BI. You can read more about this Excel feature here. One area where Excel is still ahead of Power BI Desktop is that is has an object model that lets you automate tasks with VBA. Unfortunately, Power BI Desktop doesn’t have an object model so you have to resort to unsupported ways (aka hacks) to automate tasks, such as refreshing and publishing to Power BI. I discussed some here.

I’ve noticed that Microsoft added not yet documented PublishToPBI method to Excel 2016. With it, refreshing the Excel data model and publishing it to Power BI Service takes two lines of code (you’d need more code to open the Excel workbook from an external application).

Sub Macro1()

ActiveWorkbook.Model.Refresh

ActiveWorkbook.PublishToPBI PublishType:=msoPBIExport, nameConflict:= msoPBIAbort, bstrGroupName:=”<Some Workspace>”

End Sub