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:

  1. Click on the Gear icon and Integrations (https://ehourapp.com/eh/settings/integration)
  2. Copy the API
2. Prepare the query
You can pick the columns you want to get from API or use the default selection which Client, Project, User, Hours, Turnover, Comment.
To select your own:
  1. Navigate to the API documentation, the link is on the Integrations page or use https://ehourapp.com/api/swagger-ui.html#!/report/reportKeyValueUsingGET
  2. Click on Authorize and fill out your api_key in the value field. Click Authorize
  3. Open the Report API section and the /v1/report-kv endpoint
  4. In the list of columns; copy all the columns you need separating each with a comma
  5. Make sure to set aggregation to NONE.
  6. Click on Try it out!
  7. Copy the Request URL. This is the URL that Power Query is going to call.
3. Setting up the connection in Excel or Power BI

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.

https://ehourapp.com/api/v1/report-kv?start=2020-5-1&end=2020-5-30&page=1&aggregation=NONE

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.

Click OK

4. Transforming the data

Now that the data is in Power Query we can transform it:

  1. Double click on the data: List row
  2. In the next screen, click on Click on To Table
  3. Now that the data is Converted, click on the Expand icon next to the Column1 header
  4. Select the columns you want to import and then OK Close & Load
  5. Mind that time is stored in minutes although the column is still called hours - for legacy reasons
In screens:
1. Click on the data:List row

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

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us