{"id":85947,"date":"2020-01-02T21:26:09","date_gmt":"2020-01-02T21:26:09","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=85947"},"modified":"2024-09-03T20:15:21","modified_gmt":"2024-09-03T20:15:21","slug":"power-bi-and-excel","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/power-bi-and-excel\/","title":{"rendered":"Power BI and Excel"},"content":{"rendered":"

Power BI<\/strong> and Excel<\/strong> are tools so close to each other that Power BI<\/strong> was born inside Excel<\/strong>. Power Query<\/strong>, Power Pivot<\/strong>, Power View<\/strong>, they were all Excel<\/strong> plugins before being united and become Power BI<\/strong>.<\/p>\n

The ability to build an ETL<\/strong>, design a model and generate reports and dashboards with great storytelling make Power BI<\/strong> a very powerful tool even beyond the self-service tool which initially was its purpose, but getting closer to an enterprise tool.<\/p>\n

However, Excel<\/strong> still has a unique level of flexibility for data analysts. Pivot Tables<\/strong> allow the data analyst to have a view on the model and easily mix measures and dimensions to view a result, even easier then Power BI<\/strong> reports.<\/p>\n

When Power BI was created, we easily mistake it as a visualization tool (some people still do this mistake). From that time, power bi has evolved to become very close to an enterprise-level tool.<\/p>\n

Nowadays, we have the ability to build powerful ETL<\/strong> architectures building many re-usable dataflows, build models using the ETL<\/strong> as a source and make them re-usable as Datasets published in the portal and create many reports and dashboards from these models.<\/p>\n

All these abilities still don’t replace the self-service capability in Excel Pivot Tables<\/strong>. Their flexibility for analysis is unique. So, it’s no surprise that on the way to becoming an enterprise tool Power BI<\/strong> offers Excel connections directly to its datasets.<\/p>\n

There are two solutions to make this kind of connection, let’s try.<\/p>\n

Analyze in Excel<\/h3>\n

The first option is the use of the instruction Analyze in Excel<\/strong>. You can find this option on the “…”<\/strong> menu item close to each dataset in the portal.<\/p>\n

\"Analyze<\/p>\n

\nThe idea behind this technique is simple: Power BI<\/strong> is built over the same engine than the tabular model in SSAS<\/strong>. Due to that, you can make a connection to Power BI<\/strong> datasets using Analysis Server<\/strong> as a source.<\/p>\n