Power Query
dRofus supports use of Power Query that enables users to seamlessly import, update and re-shape data from within Excel, PowerBI and other Microsoft products. Use of Power Query can e.g. reduce the need to upload Excel templates to dRofus as you will now be able to update your Excel spreadsheet directly from Excel.
Power Query can be used from a wide range of Microsoft products. In this page we will focus on how to use it with Excel. Read more about Power Query here; https://docs.microsoft.com/en-us/power-query/
Not all Built in Excel exports supports Power Query. Currently following exports are supported;
Items
Occurrences
Room Templates
Items in Room Template
Room Function
Rooms
Products
Users
You can see a Power Query icon in the top right corner for the exports that are supported.
Setting up Power Query between dRofus and Excel
There are a few steps you need to do to create an Excel you can update;
Create a query in dRofus to use in Excel
Open Excel and insert Query created by dRofus
Credentials/Login
1. Create a query in dRofus to use in Excel
In this example, we will use the export Rooms to create our export. Click on the Rooms export and select which fields you want to include in your export. In the image beneath we have included a mix of Room Core and Room Data fields.
Note: If using filters, when available, Is Empty is a supported selection.
Then select PowerQuery in the top right corner of the export detail window.
When you select Power Query, a new dialogue will open with all necessary information for creating our Excel export.
The query we are going to use to import/update data from dRofus to Excel. The query will change based on which fields you have included in the export. The Power query will also include details about server, database and project ID.
API request URI - to use in other applications that do not support PowerQuery. It will change based on which fields you have included in the export.
Selecting the Is Empty option on a field's filter will display here as eq null.
API Key is used for authentication in Excel
2. Open Excel and insert Query created by dRofus
In Excel go to Data → Get & Transform Data → Get Data → From other sources → Blank Query:
You will now open the Power Query Editor. Go to Home → Query → Advanced Editor and remove all content in the query;
Now we are going to copy the query created by dRofus and paste it into the blank query. Select all text in the Power Query field in dRofus and paste it into the blank query in Excel and press Done
After the query has been processed you will see the columns have been set up in Excel. Chose Close and Load to load the data into your Excel spreadsheet.
3. Credentials/Login
The first time you connect to a data-source from Excel you will be asked how to connect and insert your credentials;
Select Edit Credentials and select Basic. Insert your credentials as the example below
For username, please use:
apikey
For password, copy the API key from dRofus' PowerQuery window as shown below
If another user want to access the spreadsheet and update data, the user will need to authenticate with their API key as shown in the steps above.