Exporting hours to Excel and 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 but use an API key to authenticate.

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

2. Prepare the query

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

Armed with the API key to authenticate and the URL to call, start Excel, start a new blank workbook and via Data, Get Data, From Other Sources, click "Blank Query".

In the function to execute, paste the following code and replace the [URL] with the path from eHour URL you generated earlier. For example, if your URL is https://ehourapp.com/api/v1/report-kv?start=2021-01-01&end=2022-08-01, use /api/v1/report-kv?start=2021-01-01&end=2022-08-01 as the baseRelativePath.

Replace API KEY with your API key. 

Make sure to put double-quotes around the URL and API Key and not put any spaces before the first "=":

= let
    baseRelativePath = "[URL]",
    apiKey = "[API KEY]",
    baseUrl = "https://ehourapp.com",
    
    getPage = (relativePath) as record => 
        let
            source = Json.Document(Web.Contents("https://ehourapp.com", [RelativePath=relativePath, Headers=[#"X-API-Key"=apiKey]])),
            nextLink = try source[_links][next][href] otherwise null,
            data = try source[data] otherwise null,
            MyResponse = [next = nextLink , data = data]
        in 
            MyResponse,

    GeneratedList = 
        List.Generate(
            () => [res = getPage(baseRelativePath)],
            each [res][data] <> null,
            each if [res][next] = null then [res = [data = null]] else [res = getPage( Text.Range([res][next], Text.Length(baseUrl)) )],
            each [res][data]
        ),

    Data = 
        List.Combine(GeneratedList)
    in
        Data

It looks like this:

Click "Edit Credentials" and select "Anonymous" and press "Connect". We are actually not authenticating anonymously but using the API key. Excel does not recognize the API key as an authentication method.

4. Transforming the data

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

1. Click on "To Table" and in the pop-up click Ok
2. Click on the Expand icon next to the Column1 header
3. Select the columns you want to import and then OK Close & Load
4. Mind that time is stored in minutes although the column is still called hours - for legacy reasons

2. Click on the Expand icon next to the Column1 header

3. Select the columns you want to import and then OK Close & Load

4. Finally, click on and convert the data Close & Load