Saving Workbook File

Since Microsoft Excel is one of the most widely used business applications around and considered as one of the most common ways to get our data into Power BI. Also, Power BI supports importing or connecting to workbooks that are created in Excel 2007 and later. Moreover, workbooks must be saved as .xlsx or .xlsm file type and be under 1 GB. It is very important to where your workbook file is saved.

Local 

• Firstly, when we save our workbook file to a local drive on the computer or another location in your organization, from Power BI we can load the file into Power BI.
• Secondly, your file will actually remain on your local drive, so the whole file is not imported into Power BI. Thereafter, a new dataset is created in Power BI and data and the data model (if any) from the workbook are loaded into the dataset.
• Then, if your workbook has any Power View sheets, then it will appear in your Power BI site under Reports. Excel 2016 also has the Publish feature (under the File menu).
• Lastly, using Publish is the same as using Get Data > Files > Local File from Power BI, but is often easier to update your dataset in Power BI in case you are regularly making changes to the workbook.

OneDrive – Business 

• Firstly, in case you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, considered as one of the most effective ways to keep your work in Excel and your dataset, reports, and dashboards in Power BI in-sync.
• Since both Power BI and OneDrive are in the cloud, therefore Power BI connects to your workbook file on OneDrive about every hour.
• Further, in case any changes are found, then your dataset, reports, and dashboards are automatically updated in Power BI.
• Lastly, if you have saved your workbook to a local drive, then you can also use Publish to update your dataset and reports in Power BI immediately. Else Power BI will automatically synchronize, usually within an hour.

OneDrive – Personal 

• Firstly, when we save our workbook files to our own OneDrive account, then we get many of the same benefits as we would with OneDrive for Business.
• Secondly, one of the biggest difference is when we first connect to the file (using Get Data > Files > OneDrive – Personal) we would need to sign in to your OneDrive with your Microsoft account. In general, it is usually different from what you use to sign in to Power BI.
• Lastly, while signing in with your OneDrive with your Microsoft account, make sure to select the Keep me signed in option. Therefore, Power BI will be able to connect to your workbook file about every hour and make sure your dataset and reports in Power BI are in-sync.

SharePoint Team-Sites 

Now, saving our Power BI Desktop files to SharePoint – Team Sites is somewhat similar to saving to OneDrive for Business. But, one of the most prominent difference is how we connect to the file from Power BI. Accordingly, we can specify a URL or connect to the root folder.