{"id":71729,"date":"2017-07-17T14:20:22","date_gmt":"2017-07-17T14:20:22","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71729"},"modified":"2021-09-15T13:22:19","modified_gmt":"2021-09-15T13:22:19","slug":"best-worlds-using-excel-power-bi-together","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/best-worlds-using-excel-power-bi-together\/","title":{"rendered":"The Best of Both Worlds: Using Excel and Power BI Together"},"content":{"rendered":"

There are many IT technologies available for storing data, but Excel remains the most popular data tool for users of IT in businesses. Excel is just so versatile, but the downside is that everything remains in your local machine. You can\u2019t share the data, it doesn\u2019t get backed up and it isn\u2019t so secure. However, by using Power BI app too, you can view, analyze and share Excel files on the web.<\/p>\n

In first section of article you will see, how to Publish an Excel file \u2018as is\u2019<\/em> to Power BI app, create a dashboard in Power BI and share components of an Excel file to other users. The second section will cover how to create a data model in Excel, export the Excel data model to Power BI and then create visualizations in the Power BI app. We will also explain how to analyze any Power BI dataset in Excel. In the final third section, we will cover the topic of how to import data from various data sources into Power BI.<\/p>\n

<\/a>Publish Excel file to Power BI, create dashboard in Power BI and share with others<\/h2>\n

In our daily work, many of us use Excel in one or other way. Once we finished doing our data massaging and creating visualization, we are ready to share the file with others. Very often, people will share an Excel workbook with others by attaching the Excel file in an email and sending an email to several people. Later on, if they find any problem with the Excel file or if they have done some enhancements to it, they have to send it again. This is a bad idea because the Excel file may contain a large amount of data which will clog the network and the recipients are likely to use the wrong version. We need a better way to share Excel file.<\/p>\n

In Power BI, we can view the Excel file as is, we can select Excel chart and share selected Excel chart with others. Once you publish Excel file to Power BI, it is easy to reflect any changes in the original file to the publication in Power BI.<\/p>\n

First, let\u2019s publish an Excel file to Power BI. Open Excel and copy the data in the table into Excel. In Excel, select the data in the \u2018Employee\u2019<\/em> and \u2018Sales\u2019<\/em> fields, click on the \u2018Insert\u2019<\/em> tab and select \u2018bar\u2019<\/em> chart. Now in the Excel file, we have some data and a bar chart. Our Excel file is ready to save and publish.<\/p>\n\n\n\n\n\n\n\n\n
\n

Employee<\/strong><\/p>\n<\/td>\n

\n

Sales<\/strong><\/p>\n<\/td>\n

\n

Client<\/strong><\/p>\n<\/td>\n

\n

Product<\/strong><\/p>\n<\/td>\n

\n

Industry<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n

\n

John<\/p>\n<\/td>\n

\n

100<\/p>\n<\/td>\n

\n

ABC<\/p>\n<\/td>\n

\n

Long Term Equity<\/p>\n<\/td>\n

\n

Real Money<\/p>\n<\/td>\n<\/tr>\n

\n

David<\/p>\n<\/td>\n

\n

200<\/p>\n<\/td>\n

\n

XYZ<\/p>\n<\/td>\n

\n

Short Term Corporate Bond<\/p>\n<\/td>\n

\n

HedgeFund<\/p>\n<\/td>\n<\/tr>\n

\n

Lina<\/p>\n<\/td>\n

\n

600<\/p>\n<\/td>\n

\n

PQR<\/p>\n<\/td>\n

\n

Future<\/p>\n<\/td>\n

\n

HedgeFund<\/p>\n<\/td>\n<\/tr>\n

\n

Stephanie<\/p>\n<\/td>\n

\n

300<\/p>\n<\/td>\n

\n

ABC<\/p>\n<\/td>\n

\n

Long Term Equity<\/p>\n<\/td>\n

\n

Real Money<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

Save Excel file as \u2018employee_Excelbook.xlsx<\/em>\u2019. Click on the \u2018File\u2019<\/em> menu and click \u2018Publish\u2019<\/em>. To publish Excel file in Power BI, you need to \u2018sign in<\/em>\u2019 using Power BI app account. You can use your \u2018Office 365\u2019 account or you can switch to another account.<\/p>\n

<\/p>\n

Once you sign in using your Power BI account, you can select the option \u2018Upload your workbook to Power BI\u2019<\/em>. This \u2018Upload your workbook to Power BI\u2019 <\/em>option is intended for publishing an Excel file in Power BI.<\/p>\n

<\/p>\n

Once file is published, you will see the message \u2018PUBLISH TO POWER BI Workbook uploaded successfully\u2019\u2019. Open \u2018app.powerbi.com\u2019 <\/em>and sign in with your Power BI account. In \u2018My Workspace\u2019, you will see different tabs.<\/p>\n

In Power BI, under \u2018My Workspace\u2019<\/em>, there are 4 tabs.<\/p>\n