Forum Discussion

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago

Connecting Harvest API with PowerBI

Hi,   I'm trying to connect Harvest and a PowerBI dashboard through an API. I managed to connect the data through a web connection and pass the credentials stage,  however, only 100 rows/1 page of ...
Need Help
mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Can you share your M code from the Advanced Editor and/or an example of what your data look like at the step before the error?

 

Pat

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago

Thanks mahoneypat . Here is the code:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = List.Numbers(0,10,100),
#"Converted to Table" = Table.FromList(#"Changed Type", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.AddColumn(#"Changed Type", "Custom",each Json.Document(Web.Contents(BaseURL&"100"&[Column1]))))
in
#"Added Custom"

 

Current Value of "BaseURL": "https://api.harvestapp.com/v2/time_entries?from=" & "2021-01-01&to=2021-07-31"&"access_token=184XXXX&account_id=105XXXX"

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Two issues I saw.

     

    1. You were missing a & in your BaseURL

    2. Your Added Custom step was referencing the Changed Type step but should have pointed to the Converted to Table step.

     

    Here is some updated code that seems to work.

     

    let
    BaseURL = "https://api.harvestapp.com/v2/time_entries?from=" & "2021-01-01&to=2021-07-31"&"&access_token=184XXXX&account_id=105XXXX",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = List.Numbers(0,10,100),
    #"Converted to Table" = Table.FromList(#"Changed Type", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Json.Document(Web.Contents(BaseURL&"100"&[Column1])))
    in
    #"Added Custom"

     

    Pat