Import hours in Excel or Power BI
There are many ways to extract tracked time from eHour. You can export a report to Excel, Schedule a report to email or you can use Microsoft's Power Query to pull data into Excel or Power BI via the eHour API.
The benefit of importing data using Power Query is that you do not need to log in to eHour to extract data.
The initial set-up is lengthy but only needed once.
1. Get your eHour API Key
You need to authenticate yourself with the endpoint using the API key. Get your API key by logging in to eHour with Admin privileges:
- Click on the Gear icon and Integrations (https://ehourapp.com/eh/settings/integration)
- Copy the API
2. Prepare the query
- Navigate to the API documentation, the link is on the Integrations page or use https://ehourapp.com/api/swagger-ui.html#!/report/reportKeyValueUsingGET
- Click on Authorize and fill out your api_key in the value field. Click Authorize
- Open the Report API section and the /v1/report-kv endpoint
- In the list of columns; copy all the columns you need separating each with a comma
- Make sure to set aggregation to NONE.
- Click on Try it out!
- Copy the Request URL. This is the URL that Power Query is going to call.
In Excel, start a new blank workbook and via Data, Get Data, From Other Sources, click From Web.
You can use a web connector in Power BI. It's documented on the Microsoft site: https://docs.microsoft.com/en-us/power-query/connectors/web
In the pop-up, select Advanced.
If you have selected your own columns in step 2.7, paste the Request URL in the URL parts input field. To select the default columns, paste the following URL in that field. Change the start- and end-date accordingly.
Add your API key as a header by clicking "Add header", set "X-API-Key" as the key, and your eHour API key from step 1.2 as the value.
4. Transforming the data
Now that the data is in Power Query we can transform it:
- Double click on the data: List row
- In the next screen, click on Click on To Table
- Now that the data is Converted, click on the Expand icon next to the Column1 header
- Select the columns you want to import and then OK Close & Load
- Mind that time is stored in minutes although the column is still called hours - for legacy reasons
2. Click on To Table
3. Once Converted, click on the Expand icon next to the Column1 header
4. Select the columns you want to import and then OK
5. Finally, click on and convert the data Close & Load