Instructions and tutorials
1. Making connection between Power BI desktop and your dataset
Open Power BI desktop. Select Get data > Power Bi datasets. Find your dataset and click connect . If you would like to add additional sources to the dataset (like excel file), then click in the bottom right corner on the:
Add a local model > submit.
Now you can start working on your report.
Once your created report is complete, click on ‘Publish’. Save the file and choose the workspace where you intend to publish the report. Now, in your workspace, you can add the report to your app by toggling the ‘Included in app’ switch.
2. Adding Excel file from SharePoint to the local data model
2.1 Straight from SharePoint
Go to your SharePoint page:
– There are folders in Documents pane
– Locate the Excel file
– Click the three dots ellipsis button
– Click Details
In the Details pane, scroll down in the pane to find the Path section
Click the Copy Direct Link button
In Power BI Desktop, click Get Data, then select Web and paste the link in the URL text box.
Note 3. After making changes in excel file (changing the file location, changing columns etc.) you need to check connections again with the model to make sure everything still works.
2.2 From Teams
From Teams in General Channel select Files tab and Open in SharePoint.
From SharePoint copy the URL to the level of the site.
Example:
https://yourcompany.sharepoint.com/sites/yoursitename
In power BI desktop select
– Get data
– SharePoint folder and paste your URL
– select Transform Data
– Find the file you would like to add to Power Bi and next to that file in the Content column click on Binary
– – select which data you would like to load to the dataset by clicking on Table in Data column
– If your data is not formatted as a table in Excel, then click on the Use first row as a Headers on the top pane
– Close and apply
3. Adding Excel file from Google Sheets to the local data model
- Connecting Power BI Desktop to Google sheets
- Open Power BI desktop
- Select Get data > More..
- Write Google Sheets to the search box and connect (it might give you a warning, press continue)
- In the URL box paste the file URL that you want to add to Power BI (open the file in the Google Sheets and copy the URL) and press OK.
- Sign in to your Google account and connect
- Select sheets/tables you want to add and select Transform data.
- If your data is not formatted as a table in Excel, then click on the Use first row as a Headers on the top pane
- Close and apply
4. Enabling API calls in SmartAccounts
In SmartAccounts all the API calls that we use in our dataset are not enabled by default. In order to allow API calls, you need to take these steps:
– Log in to your SmartAccounts account and select Settings (the ratchet in the upper right corner)
– Select Users & groups from the left menu
– Under user group click on API
– Add the following rights to the user group (search and click on them so they would go to other column):
• Settings – Report settings (for reportrows table)
• Settings – Template settings (for documenttemplates table)
• Payroll – Employees (for payouttypes, workers, absencetypes tables)
• Payroll – Time off (for workerabsences table)
– Save
