将 Clockify 数据加载到 Power BI

问题描述

我有一份基于 Clockify 数据的 Power BI 报告.到目前为止，我必须从 clockify 下载详细报告，以便能够在 Power BI 中查看最新数据.我想摆脱这个手动步骤，直接使用 API 连接到 Clockify 中存储的数据.

I'm having a Power BI report which is based on data from Clockify. So far I had to download the detailed report from clockify in order to be able to see up to date data in Power BI. I would like to get rid of this manual step and directly connect to the data stored in Clockify using the API.

我目前正在加载以下 M 代码:

I'm currently loading with following M code:

let
    Query1 = let
Source = Json.Document(Web.Contents("https://api.clockify.me/api/workspaces/",
[Headers=[#"x-api-key"="xxxxxxxxxxx"]])),
messages = Source[messages]
in
Source

这会导致 Power Query 中的记录，然后我可以将其转换为表格

但是当我展开表格时，我只能获得有关我的 Clockify 工作区的信息，而不是记录的小时数.我希望直接在 Power BI 中提供详细报告中的所有信息.希望有人能帮忙.干杯！

But when I expand the table, I only get information about my Clockify Workspace but not the recorded hours. I would like to have all information in the detailed report available directly in Power BI. Hope somebody can help. Cheers!

推荐答案

我复制了您的查询，但我认为您点击的端点没有您想要的信息.您必须在 API 文档中搜索它.

I replicated your query and I don't think that the endpoint you're hitting has the information you want. You'll have to search for it in the API documentation.

无论如何，这是扩展该端点中所有内容的M代码，看看它是否有用:

Anyways, here is the M code for expanding everything in that endpoint, see if it has something useful:

let
    Query1 =
    let
        Source = Json.Document(Web.Contents("https://api.clockify.me/api/workspaces/",
            [Headers=[#"x-api-key"="YOUR-API-KEY"]])),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "hourlyRate", "memberships", "workspaceSettings"}, {"id", "name", "hourlyRate", "memberships", "workspaceSettings"}),
        #"Expanded workspaceSettings" = Table.ExpandRecordColumn(#"Expanded Column1", "workspaceSettings", {"timeRoundingInReports", "onlyAdminsSeeBillableRates", "onlyAdminsCreateProject", "onlyAdminsSeeDashboard", "defaultBillableProjects", "isProjectPublicByDefault", "lockTimeEntries", "round", "projectFavorites", "canSeeTimeSheet", "projectPickerSpecialFilter", "forceProjects", "forceTasks", "forceTags", "forceDescription", "onlyAdminsSeeAllTimeEntries", "onlyAdminsSeePublicProjectsEntries", "trackTimeDownToSecond", "projectGroupingLabel", "adminOnlyPages", "automaticLock", "onlyAdminsCreateTag"}, {"timeRoundingInReports", "onlyAdminsSeeBillableRates", "onlyAdminsCreateProject", "onlyAdminsSeeDashboard", "defaultBillableProjects", "isProjectPublicByDefault", "lockTimeEntries", "round", "projectFavorites", "canSeeTimeSheet", "projectPickerSpecialFilter", "forceProjects", "forceTasks", "forceTags", "forceDescription", "onlyAdminsSeeAllTimeEntries", "onlyAdminsSeePublicProjectsEntries", "trackTimeDownToSecond", "projectGroupingLabel", "adminOnlyPages", "automaticLock", "onlyAdminsCreateTag"}),
        #"Expanded memberships" = Table.ExpandListColumn(#"Expanded workspaceSettings", "memberships"),
        #"Expanded memberships1" = Table.ExpandRecordColumn(#"Expanded memberships", "memberships", {"userId", "hourlyRate", "targetId", "membershipType", "membershipStatus"}, {"userId", "hourlyRate.1", "targetId", "membershipType", "membershipStatus"}),
        #"Expanded hourlyRate" = Table.ExpandRecordColumn(#"Expanded memberships1", "hourlyRate", {"amount", "currency"}, {"amount", "currency"}),
        #"Expanded round" = Table.ExpandRecordColumn(#"Expanded hourlyRate", "round", {"round", "minutes"}, {"round.1", "minutes"}),
        #"Expanded adminOnlyPages" = Table.ExpandListColumn(#"Expanded round", "adminOnlyPages")
    in
        #"Expanded adminOnlyPages"
in
    Query1

如果您不使用 Postman 和 Fiddler，我建议您使用它来测试端点并检查响应.

If you are not using Postman and Fiddler, I recommend it for testing the endpoints and check the responses.

这篇关于将 Clockify 数据加载到 Power BI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！