Synergy Data Export - Excel for Power BI

1. Power BI online steps:
   1. Go to www.powerbi.com and sign-in or create a new user account.
   2. Download the Power BI Desktop application here: https://powerbi.microsoft.com/en-us/desktop/
   3. Install the Power BI Desktop application following the steps shown on the above page.
2. Synergy Cloud Services steps:
   1. Login to Synergy Cloud Services at www.synergycloudapp.com using an account that has either 'Director' or 'System Administrator' level access to the 'app' area.
   2. Open the 'App' area.
   3. Select the menu button in the top toolbar > select the 'Reports' option.
   4. Select the 'Synergy data export' button to open the Synergy Data Export page.
   5. Apply the required 'export settings' to:
      • Include projects or sub projects based on a 'status' filter. e.g. exclude items that are 'unsuccessful' status.
      • Set the 'from date' to a max of 2 years.
        Note: The date filter the data in the output file for the tables: transactions, invoice and credits, forecast, and status change tables for projects and sub projects.
      • Select if credit notes or cancelled invoices will be included in the export file.
   6. Click the 'Rebuild data now' button and the excel export file will be created, and automatically download a file called 'Synergy Data.xlsx' to your PC.
   7. Click the 'Get the Synergy sample file' button, to download the Power BI sample file to your PC called 'Synergy Data.pbix'.
3. Your PC steps:
   1. On your local computer create a folder called 'Synergy Power BI' in the C drive of your computer. This will then have the following path: C:\Synergy PowerBI
   2. Open the 'Downloads' folder you on your PC using Windows Explorer.
   3. Copy the files you downloaded from Synergy Cloud Services - 'Synergy Data.xlsx' and 'Synergy Data.pbix'.
   4. Paste the files into this folder (created in step 3) C:\Synergy PowerBI.
   5. Open the file 'Synergy Data.pix'. This will open the Power BI Desktop application.
4. Power BI Desktop Application steps:
   1. The Power BI sample file opens to show the reports using data from a Synergy demo environment.
   2. Click the 'refresh' button in the top toolbar of Power BI, and this will load in your companies data to the tables.
   3. After the data load of your companies data has completed, the dashboard reports will update to display your data.
   4. (Optional) Adjust these reports to meet any of your companies reporting requirements.
   5. Publish the reports to the Power BI online portal. Learn more in the Power BI documentation.
5. Power BI online portal steps:
   1. Open the online Power BI at www.powerbi.com.

   2. Setup your Power BI dashboard by 'pinning' your favourite reports to a dashboard. Follow the steps in the Power BI documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-dashboards/

   3. (Optional) Share your reporting dashboard with your team in Power Bi. Follow these steps in the Power Bi documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-share-unshare-dashboard/

1. Contacts
2. Projects
3. Sub Projects
4. Transactions
5. Expenses
6. Invoices
7. Invoice Lines
8. Primary Project Contacts
9. Project Invoice Contacts
10. Invoice To Contacts
11. Personnel
12. Staff
13. Project Managers
14. Sub Project Managers
15. Project Attributes
16. Contact Attributes
17. Personnel Attributes
18. Sub Project Attributes
19. Staff Attributes
20. Forecast
21. Project Status Change
22. Sub Project Status Change
23. Dates

While setting up a sample file for Synergy users to have for Power BI, I found out a few items that might help out new users for Power BI Desktop. Here are the tips and tricks we learnt whilst creating this sample file for you to have as a starting point.

The sample file we provided in Synergy Cloud Services has already had all of these tips applied to save you time. This would only be required if you wanted to create your own Power BI file from start to finish using the Excel import method.

Where	Overview	Description
Excel	Table format CTRL T	Any data that will be imported into Microsoft Power BI should be converted into 'table' Excel format. If you manually run a Synergy report to Excel, then use the keyboard shortcut CTRL T to convert the report output to Excel table format.
Excel	Unformatted table	The table that will be imported should be unformatted, and not have any pivot tables on the same worksheet as the data source. Pivot tables can be on other worksheets in the file, just not the worksheet tab that will be imported into Power BI.
Excel	Multi tabbed file	Power Bi lets you imported all the tabs / worksheets from the one Excel file. This lets you run many reports out of Synergy to the one Excel file (Project, Contacts, Invoices etc), and they can all be imported into Power BI.
Power BI	File location	The Excel file can be imported into the Power BI Desktop application.
Power BI	Data refresh	To make data refresh easy, you should create a folder that will always contain the Excel file to be imported. e.g. C:\Power BI Data folder. In this folder save the file with the same name every time, and the you can just overwrite this file with the new Excel data and click the 'refresh' button in Power BI to import the latest excel data set. If you are manually creating the excel file, ensure that there are not any blank rows in the tables.
Power Bi and Excel	New columns	If the Excel file is updated to include extra column at a later date, then Power Bi will 'auto' add the columns to the tables as long as they are in at the end of the current table list of columns, and that they are within the 'table' range. e.g. The existing columns are not moved around in any way, and the new columns are added to the end of the current set.
Power BI	Linked columns Relationships	When the column is named the same in multiple tables, a relationship link in Power Bi is automatically created between the matching columns when the data table is imported for the first time. e.g. Project # column in a Projects report would auto linked to a Project # column if included in the Invoices report. Check what relationships Power BI automatically creates using the 'Manage Relationships' option, as there can only be one 'active' link from any one table at a time. This will impact what reporting options you have in Power BI.
Power Bi and Excel	Rename columns	Do not change column names or re-order the columns in the excel file, as then the Power BI file is invalid and cannot import the data. When this happens the user will either need to 'delete the table' from Power BI and then re-setup the relationship links, or use the query editor to manually update the column names in Power BI. Suggest that all new columns should only be added to then end of the worksheet list of columns, and not moved or re-ordered.
Power BI	Mapping data	In the table view locate any 'address' type fields and add a data category. This lets Power BI know if the column contains town or suburb names, states, country names etc. Power BI can then use this address data in the 'map' type reports.
Power BI	Currency, dates, percentages	Power BI doesn't automatically pick-up the Excel cell formatting. This means you need to locate any 'new' columns of data in the tables that you add-in, and let Power BI know what 'date format' to use, or if the number is a currency field, or if it is a percentage etc.
Power BI	Custom calculated columns	With calculated columns, you can add new data to a table already in your model. Learn more about how this works by reading the Power BI guides here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-calculated-columns/
Power BI (online or apps)	Share a dashboard	Using the online portal for your data at www.powerbi.com you can share the created reports with your team. These dashboards can be just shared with internal staff, or also shared with external parties. Learn more about the sharing options here:https://powerbi.microsoft.com/en-us/documentation/powerbi-service-share-unshare-dashboard/