Replace date range with parameters in Power Query

Developers & API
1

Hello, I’m trying to use PowerQuery in Excel to pull in the detailed report. People using the Excel workbook need to be able to update the start and end dates within a named cell in the workbook. I’m struggling to replace the date strings with a reference to those named cells or parameters. Can anyone please help?

Rather than:
let
body = "{ ““dateRangeStart””: "“2024-01-01T00:00:00.000"”, ““dateRangeEnd””: "“2024-12-31T23:59:59.000"”, ““detailedFilter””: { ““page””: "“1"”, ““pageSize””: ““1000"” } }”, Source = Json.Document

Something like:
let
body = "{ ““dateRangeStart””: “[StartDate]”, ““dateRangeEnd””: “[EndDate]”

2

Hi there,

I also struggeled a while with this problem and finally solved it. Here is a code snippet depicting my solution:

let
    //Parameter lesen
    apiKey = Excel.CurrentWorkbook(){[Name="ApiKey"]}[Content]{0}[Column1],
    wsId = Excel.CurrentWorkbook(){[Name="WorkspaceID"]}[Content]{0}[Column1],

    startDate = Excel.CurrentWorkbook(){[Name="Start"]}[Content]{0}[Column1],
    endDate = Excel.CurrentWorkbook(){[Name="End"]}[Content]{0}[Column1],
    prjId = Excel.CurrentWorkbook(){[Name="ProjektID"]}[Content]{0}[Column1],

    //URL konstruieren
    url="https://reports.api.clockify.me/v1/workspaces/" & wsId & "/reports/detailed/",
    
    //Abfrage aufbauen
    content =
       "{""dateRangeStart"": """ & startDate & """,""dateRangeEnd"": """ & endDate & """
        ,""detailedFilter"": {""page"": ""1"",""pageSize"": ""1000"",""sortColumn"": ""DATE""}
        ,""sortOrder"": ""ASCENDING"",""exportType"": ""JSON""
        ,""projects"":  {""ids"":[""" & prjId & """]}
        ,""amountShown"": ""EARNED"",""rounding"": ""true""
        }",      
    Source = Json.Document(
                     Web.Contents(url,    [Headers=    [ #"X-Api-Key"=apiKey
                                                         ,ContentType="application/json"
                                                         ,#"Content-Type"="application/json"
                                                        ]
                                          ,Content=Text.ToBinary(content)
                                          ] 
                                  )
                              ),
    timeentries = Source[timeentries],

Hope this helps you - regards,
Holger

3

Thank you! Very helpful.

4

You are welcome!

best regards,
Holger