How to Setup Xero connection to Power BI through xero api

[DiKi-I]

Has anyone used xero api to pull the data in power bi using m-query? I don't want to use any third party connector.

[Ritaf1983]

Hi @DiKi-I 

Maybe you can try something like:

To use the Xero API to pull data into Power BI using M Query without a third-party connector, follow these steps. This involves making HTTP requests directly to the Xero API in M Query. The process typically includes:

Registering an App in Xero: Register an app in the Xero portal to obtain the required credentials (Client ID and Client Secret).
OAuth 2.0 Authentication: Implement the OAuth 2.0 flow to get an Access Token for API access.
M Query Scripting: Write M Query in Power BI to send requests to the API using functions like Web.Contents.
Here is an example of basic M Query code to make an API request:

m
Copy code
let
BaseUrl = "https://api.xero.com/api.xro/2.0/Invoices",
Headers = [Authorization = "Bearer YOUR_ACCESS_TOKEN"],
Source = Web.Contents(BaseUrl, [Headers = Headers])
in
Source
For more detailed guidance, you may refer to general documentation on OAuth 2.0 authentication 

https://developer.xero.com/documentation/oauth2/auth-flow

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[DiKi-I]

Thank you, I'm trying to register the app, could you help me with redirect URI and how I can generate access token -OAuth 2.0 Authentication: Implement the OAuth 2.0 flow to get an Access Token for API access.

[Housden996]

Hi if youre still having an issue W this you can use the following M Query, invoice table as an example:

let
authUrl = "https://identity.xero.com/connect/token",

formBody =
[
   grant_type = "client_credentials",
   client_id = "your id",
   client_secret = "your secret",
   scope = "accounting.transactions.read"
], 

AuthResponse= Web.Contents(authUrl,
[Headers=[#"Content-Type" = "application/x-www-form-urlencoded"],Content = Text.ToBinary(Uri.BuildQueryString(formBody))]),

AuthJson = Json.Document(AuthResponse),
    #"Converted to Table" = Record.ToTable(AuthJson),
    token = #"Converted to Table"{0}[Value],

bearer = Text.Combine({"bearer", token}, " "),

    Source = Xml.Tables(Web.Contents("https://api.xero.com/api.xro/2.0/Invoices", [Headers=[Authorization=bearer]])),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Status", type text}, {"ProviderName", type text}, {"DateTimeUTC", type datetime}}),
    #"Expanded Invoices" = Table.ExpandTableColumn(#"Changed Type", "Invoices", {"Invoice"}, {"Invoice"}),
    #"Expanded Invoice" = Table.ExpandTableColumn(#"Expanded Invoices", "Invoice", {"Contact", "Date", "DueDate", "Status", "LineAmountTypes", "SubTotal", "TotalTax", "Total", "UpdatedDateUTC", "CurrencyCode", "Type", "InvoiceID", "InvoiceNumber", "Reference", "AmountDue", "AmountPaid", "AmountCredited", "SentToContact", "CurrencyRate", "HasAttachments", "HasErrors", "FullyPaidOnDate", "Payments", "ExpectedPaymentDate", "RepeatingInvoiceID", "PlannedPaymentDate", "Url", "Overpayments", "CreditNotes", "BrandingThemeID"}, {"Contact", "Date", "DueDate", "Status.1", "LineAmountTypes", "SubTotal", "TotalTax", "Total", "UpdatedDateUTC", "CurrencyCode", "Type", "InvoiceID", "InvoiceNumber", "Reference", "AmountDue", "AmountPaid", "AmountCredited", "SentToContact", "CurrencyRate", "HasAttachments", "HasErrors", "FullyPaidOnDate", "Payments", "ExpectedPaymentDate", "RepeatingInvoiceID", "PlannedPaymentDate", "Url", "Overpayments", "CreditNotes", "BrandingThemeID"}),
    #"Expanded Contact" = Table.ExpandTableColumn(#"Expanded Invoice", "Contact", {"ContactID", "Name", "ContactNumber"}, {"Contact.ContactID", "Contact.Name", "Contact.ContactNumber"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Contact",{{"DueDate", type datetime}, {"UpdatedDateUTC", type datetime}, {"FullyPaidOnDate", type datetime}, {"Date", type datetime}, {"SubTotal", Currency.Type}, {"TotalTax", Currency.Type}, {"Total", Currency.Type}, {"AmountDue", Currency.Type}, {"AmountPaid", Currency.Type}, {"AmountCredited", Currency.Type}, {"ExpectedPaymentDate", type datetime}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Id", "Status", "ProviderName", "DateTimeUTC"}),
    #"Expanded CreditNotes" = Table.ExpandTableColumn(#"Removed Columns", "CreditNotes", {"CreditNote"}, {"CreditNote"}),
    #"Expanded CreditNote" = Table.ExpandTableColumn(#"Expanded CreditNotes", "CreditNote", {"CreditNoteID"}, {"CreditNoteID"})
in
    #"Expanded CreditNote"

You need to connect both as anon and refresh when you have okayed the anons

For the App in Xero we are using the redirect uri https:// oauth.powerbi.com/views/ oauthredirect.html