Excel as a Self-Service BI Tool for Power BI Data
?????????? ???? ???? ?????????????? ?????????? ???? ????????????????! Over the years, Microsoft has constantly improved the Power BI integration into Excel. For ????????-?????????????? users that are familiar with Excel, this is a ???????????????????? starting point. Meanwhile the integrations is so ?????????????????????????? that you can even use the Power BI toolbox in Excel as the ?????????????? to navigate to the Power BI reports!
????????'?? ?????????????????? ???? ?????????? ???????????
???????? ways of using Power BI data directly in Excel:
1?? As a ?????????? ??????????: Choose ???????????? > ?????????? ?????????? > ???????? ?????????? ???? and all your semantic models are available at your fingertips to feed a Pivot table.
2?? As a ?????????? ??????????: Choose ???????????? > ?????????? ?????????? > ?????????? ?????????? and then ?????? ???? ???????????????? ???????? ????????????. If you already created a Pivot table from the same semantic model, then it's listed in the existing connections. So my ?????? ?????? is: Always create a Pivot table first before creating a Pivot chart to get the connection into the workbook. You can discard the Pivot table later.
3?? As a ??????????: Choose ???????? > ?????? ???????? > ???????? ?????????? ???????????????? > ???????? ?????????? ????. Choose your semantic model and click on + ???????????? ??????????. You can select columns, measures, and filters. This is still aggregated data, not raw data, but since it just creates a DAX query that you can modify, you can modify it to ???????????????? '???? ??????????' to load ?????? ???????? or any other custom query from the Power BI semantic model! To customize the DAX query, go to ???????? > ?????????????? & ??????????????????????, right-click on your Power BI connection, choose ????????????????????..., go to the ???????????????????? tab and modify the ?????????????? ????????.
4?? ???????????????????????????? ???????? ??????????: You can ?????????????????????????? data in Excel with values from Power BI. E.g. type a cutomer number into a cell, defined it as organizational data type "Customer" and then just fill cells with name, overdue payments, last year revenue, etc. to do your analyzis. Klick the cell with the key value, e.g., customer number, then click on ????????, open ???????? ??????????, and choose ???????? ???????? ????????????????????????. This requires setup as described here: https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-create-excel-featured-tables
5?? ???????? ???????????????? ???? ?????????? ????????????????: Just for completeness, you can use CUBE formulas to get any value from Power BI into any cell in Excel for full flexibility. https://www.youtube.com/watch?v=1WRRIQAuwEs
?????? ?????????? ???? ?????????????????????? ?????? ?????????? ?????????????????????? with the latest data. So no more need to export, copy, paste, and prepare data to refresh you Excel workbooks! No plug-ins needed either.
?????? ?????????? ???? ?????????????? ????????:
Meanwhile, you can use Excel as a complete Power BI navigation cockpit. The Power BI toolbox, that opens by choosing ???????? > ?????? ???????? > ???????? ?????????? ???????????????? > ???????? ?????????? ???? allows you to:
领英推荐
? Find a semantic model
? Preview a list of tables in the semantic model to decide whether it contains what you need.
? Shows links to all associated reports so that you can ???????? ?????????? ???? ?????????????? ???????????????? ???????? ??????????!
? Allows you to open the semantic model in the Power BI web app, e.g., to create a Power BI report instead of an Excel workbook.
? Insert tables and Pivot tables into Excel, as described above.
?????????? ???? ???????? ????????:
?? You can add ?????????? ?????????????????? ???????????????? ???????? ?????????? ???? ??????????????????, so don't hesitate to build your analysis and dashboards in Excel, with data from Power BI or the Excel Power Pivot model, to ?????????? ???????? in Power BI, if this is what you feel comfortable with. This also allows you to fully leverage all the ???????????????????? options and formulas available in Excel.
?? ?????? ways of adding Excel workbooks to a ?????????? ???? ??????: Either as content from the workspace or using an embed link from Excel Online. ???????? ???? ?? ?????? to see the differences!
Which of these features do you use in your organization?
Co-Founder of Analytic Endeavors | Microsoft MVP | Owner of Sunny BI - Power BI Training and Consulting
2 个月Excel is amazing as a front-end, and should be a front-end. Honestly, an excel connected to a semantic model has often seen much more value than many reports I’ve ever built!
Passionate Data Analyst | Expert in Power Bi and Data Visualization Enabling Data-Driven Driven Insights for Business Success | LinkedIn Creator
2 个月Hey, I could really use some motivation today! If you have a moment, please check out my latest post and give it a like if it resonates with you. And if you haven’t already, I’d be so grateful if you could follow my profile. Your support means the world to me!
POWER BI or DIE Podcast & Streams ???| Microsoft Data Plattform @ BI or DIE ??| Self-Service, Agile BI and Analytics ??| Power BI Fanboy ??| Sketchnotes ??| YouTube ??| TDWI Expert & Author ??
2 个月would you think an Excel visual in PBI would be useful? ??