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