Has anyone managed to get data from FreeAgent into PowerBi?
Hi @eddablin,
I am interested in doing this as well. We have used Power BI to report on timesheet data quarterly by simply exporting all data and bringing it into Power BI via Excel, but I would now like to do this kind of analysis weekly or even daily via the API. If you have made any progress since your post I would love to hear more.
Thanks
Hi Rob,
No sadly got a bit stuck (certainly not a developer).
Thanks,
Ed
Rob,
Your message prompted me to have another go. I managed to achieve bringing in projects, contacts etc. and get around pagination so that I could have the data in one big list.
I need to do work on this and polish it up which may take me a while to find some time. I will then let you know how I did it. However, if you wanted to have a go in the meantime, these links were very useful:
Dealing with OAuth2.0 (adapting the code in the second post): https://community.powerbi.com/t5/Power-Query/Google-Oauth2-Google-My-Business-API/m-p/583211
Dealing with pagination: https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query
Hi All,
Was any progress made with this?
Yes. I am bringing all sorts of data from FA into PowerBi and Excel. It is very very useful. Here is an example of getting contacts.
I use this query to get access (this was the hard bit):
(PageStart as text) =>
let
app_credentials ="client_id=...........",
url = app_credentials & "&refresh_token=" & "......." & "&grant_type=refresh_token",
GetJson = Web.Contents("https://api.freeagent.com/v2/token_endpoint",
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(url)
]
),
AccessToken = Json.Document(GetJson)[access_token],
AccessTokenHeader = "Bearer " & AccessToken,
Source = Xml.Tables(Web.Contents("https://api.freeagent.com/v2/", [RelativePath="contacts?page="&PageStart&"&per_page=100", Headers=[#"Authorization"=AccessTokenHeader, Accept="application/xml", #"Content-Type"="application/xml"]])),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:type", type text}})
in
#"Changed Type"
I then do this query to get data:
let
Source = {1..30},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PageStartNumbers"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PageStartNumbers", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnContactData", each fnContactData([PageStartNumbers])),
#"Expanded fnContactData" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnContactData", {"contact", "Attribute:type"}, {"contact", "Attribute:type"}),
#"Expanded contact" = Table.ExpandTableColumn(#"Expanded fnContactData", "contact", {"url", "organisation-name", "active-projects-count", "created-at", "updated-at", "contact-name-on-invoices", "country", "charge-sales-tax", "locale", "account-balance", "status", "uses-contact-invoice-sequence", "emails-invoices-automatically", "emails-payment-reminders", "emails-thank-you-notes", "uses-contact-level-email-settings", "first-name", "last-name", "email", "billing-email", "address1", "town", "postcode", "address2", "default-payment-terms-in-days", "region", "address3", "sales-tax-registration-number", "mobile", "phone-number"}, {"url", "organisation-name", "active-projects-count", "created-at", "updated-at", "contact-name-on-invoices", "country", "charge-sales-tax", "locale", "account-balance", "status", "uses-contact-invoice-sequence", "emails-invoices-automatically", "emails-payment-reminders", "emails-thank-you-notes", "uses-contact-level-email-settings", "first-name", "last-name", "email", "billing-email", "address1", "town", "postcode", "address2", "default-payment-terms-in-days", "region", "address3", "sales-tax-registration-number", "mobile", "phone-number"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded contact",{"PageStartNumbers"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"url", "first-name", "last-name", "organisation-name", "active-projects-count", "created-at", "updated-at", "contact-name-on-invoices", "country", "charge-sales-tax", "locale", "account-balance", "status", "uses-contact-invoice-sequence", "emails-invoices-automatically", "emails-payment-reminders", "emails-thank-you-notes", "uses-contact-level-email-settings", "email", "billing-email", "address1", "town", "postcode", "address2", "default-payment-terms-in-days", "region", "address3", "sales-tax-registration-number", "mobile", "phone-number", "Attribute:type"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"organisation-name", "ContactID"}})
in
#"Renamed Columns1"
I think i have solved this by making the folling changes:
let
app_credentials ="client_id=......&client_secret=......&scope=&grant_type=authorization_code",
url = app_credentials & "&refresh_token=" & "......." & "&grant_type=refresh_token",
GetJson = Web.Contents("https://api.freeagent.com/v2/token_endpoint",
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(url)
]
),
AccessToken = Json.Document(GetJson)[access_token],
AccessTokenHeader = "Bearer " & AccessToken,
Source = Xml.Tables(Web.Contents("https://api.freeagent.com/v2/", [RelativePath="contacts?page="&PageStart&"&per_page=100", Headers=[#"Authorization"=AccessTokenHeader, Accept="application/xml", #"Content-Type"="application/xml"]])),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:type", type text}})
in
#"Changed Type"
It seemed like the secret was missing but all good now - i was wondering @eddablin if you have managed to navigate getting past the constantly changing refresh token?
(had to edit this reply)
Ah yes, the refresh stuff is all good, was just me being stupid.
Have you managed to dynimically pull the ‘total contact’. From reading the docuemntation you can use the X-total-count but i am struggling to get PowerBI to retreve this contact, this will them make you second part of the code dynamic so that you dont need to know the maxium pages but rather pull that directly from teh API request.
Oh I do apologise. Yes I deleted that bit off it seems by accident whilst deleting out our key info.
I don’t know what you mean by constantly refreshing token?
In fact I think I do know what you mean. I am not a programmer so very much an amateur. This code was the result of trial and error and blood, sweat and tears.
If I am right, this code uses the OAuth identifier and OAuth secret to get a new bearer access token each time.
1 Like
I’m not sure how I got the … value on the third line of code. It is possible I used OAuth 2.0 Playground at some point to get a value.
@eddablin
Got it all up and working, thank you.
Have you managed to get it published to powerBI web and can you schedule refreshes? If so how did you get past the authentication on PowerBI Web?
Yes I have refreshes scheduled. I don’t even have paid service. Think you can refresh up to 6 times a day on free service. Think it all just worked when I published and then set up the refresh. Maybe share the details of any issues you are having?
Ah yes, ive managed to work it out and all looking good! i assumed you needed to go via a gateway but nope seems to be all good.
Thank you for your help its has been immensely useful.