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;

1. Create a query in dRofus to use in Excel

2. Open Excel and insert Query created by dRofus

3. 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.

1. 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.

2. 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.

3. 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 

1. For username, please use: apikey

2. 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.