Mike Allen Systems

Doing Smarter Things With IT

Application Authenticate using public/private key pairs

Recently I’ve been using no-code and low-code solution to build small business applications. I’ve had to learn more about authentication using public/private key pairs, which I’ve normally had other people look after for me, but now it’s time for me to do it myself. After a read of the Wikipedia article https://en.wikipedia.org/wiki/X.509

First up the business scenarios. My web site uses https://letsencrypt.org/ certificates, a fantastic free service, it’s built into my web portal so I just nee to know it’s there, simple. Then there was the e-commerce site where I had to take a commercial certificate and install it on an Azure web site, a little bit of manual prep, bit largely automated by the portal.

Then something more complex, I wanted to use an Azure function to talk to SharePoint online. I checked out this post https://blogs.msdn.microsoft.com/richard_dizeregas_blog/2015/05/03/performing-app-only-operations-on-sharepoint-online-through-azure-ad/ , which told me almost everything that I needed to know. One important lesson was that the AAD app acts as a portal to SharePoint online, so once I had one app working, all my azure functions then used the same AAD app for access to SharePoint online. This is still a work in progress for me, I was motivated by a need to insert images from a Power App, then I realized that this opened the door to other business needs, such a creating a PDF from a SharePoint item.

Then I got back to my core business driver, Business Intelligence, and my current area of interest, Xero. I was looking for drag and drop access to Xero data, with a simple connector. At this point I looked at the new https://linx.software/ Xero connector, and was a little put off that I needed a Consumer Key, and a secret to use the Xero API. All too hard I thought! Then I looked close and realized if all used a certificate, I read this https://developer.xero.com/documentation/api-guides/create-publicprivate-key and then created a new cert. The I created a private Xero App with my certificate and it all fell into place.

I’m not an expert on certificates  by any measure, but now I know enough to integrate my Xero with Power BI using Linx, that will be my next adventure.

Get Invoices from Xero to Power BI using Linx

I have a side project exploring low-cost and low-code ways of interfacing with Xero. I’ve extracted data using Linx from https://linx.software/ , a useful low-cost software solution.

You need to create a private Xero App at https://developer.xero.com/documentation/auth-and-limits/private-applications , this is actually fairly simple. Generating the certificate is the most difficult bit, see my blog https://mikeallen.com.au/post/Application-Authenticate-using-publicprivate-key-pairs.

Once that is done set up a Linx job with two steps:

1/ GetInvoices

2/TextFileWrite

It’s all pretty self-explanatory and it will generate a nice json file for you.

Import the json into Power BI using Power Query, the Date formats are a bit tricky because there’s a time zone in there. I’ve fixed the ‘Date’ column to make it just a Date.

Here’s the query:

let
     Source = Json.Document(File.Contents("C:\Users\Mike\Documents\LinxOutput\LinxInvoices.json")),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Type", "Contact", "Date", "DueDate", "Status", "LineAmountTypes", "LineItems", "SubTotal", "TotalTax", "Total", "TotalDiscount", "UpdatedDateUTC", "CurrencyCode", "CurrencyRate", "InvoiceID", "InvoiceNumber", "Reference", "BrandingThemeID", "Url", "SentToContact", "ExpectedPaymentDate", "PlannedPaymentDate", "HasAttachments", "Payments", "CreditNotes", "Prepayments", "Overpayments", "AmountDue", "AmountPaid", "FullyPaidOnDate", "AmountCredited"}, {"Type", "Contact", "Date", "DueDate", "Status", "LineAmountTypes", "LineItems", "SubTotal", "TotalTax", "Total", "TotalDiscount", "UpdatedDateUTC", "CurrencyCode", "CurrencyRate", "InvoiceID", "InvoiceNumber", "Reference", "BrandingThemeID", "Url", "SentToContact", "ExpectedPaymentDate", "PlannedPaymentDate", "HasAttachments", "Payments", "CreditNotes", "Prepayments", "Overpayments", "AmountDue", "AmountPaid", "FullyPaidOnDate", "AmountCredited"}),
     #"Expanded Contact" = Table.ExpandRecordColumn(#"Expanded Column1", "Contact", {"ContactID", "ContactNumber", "AccountNumber", "ContactStatus", "Name", "FirstName", "LastName", "EmailAddress", "SkypeUserName", "ContactPersons", "BankAccountDetails", "TaxNumber", "AccountsReceivableTaxType", "AccountsPayableTaxType", "Addresses", "Phones", "IsSupplier", "IsCustomer", "DefaultCurrency", "UpdatedDateUTC", "XeroNetworkKey", "SalesDefaultAccountCode", "PurchasesDefaultAccountCode", "SalesTrackingCategories", "PurchasesTrackingCategories", "PaymentTerms", "ContactGroups", "Website", "BrandingTheme", "BatchPayments", "Discount", "Balances", "HasAttachments"}, {"ContactID", "ContactNumber", "AccountNumber", "ContactStatus", "Name", "FirstName", "LastName", "EmailAddress", "SkypeUserName", "ContactPersons", "BankAccountDetails", "TaxNumber", "AccountsReceivableTaxType", "AccountsPayableTaxType", "Addresses", "Phones", "IsSupplier", "IsCustomer", "DefaultCurrency", "UpdatedDateUTC.1", "XeroNetworkKey", "SalesDefaultAccountCode", "PurchasesDefaultAccountCode", "SalesTrackingCategories", "PurchasesTrackingCategories", "PaymentTerms", "ContactGroups", "Website", "BrandingTheme", "BatchPayments", "Discount", "Balances", "HasAttachments.1"}),
     #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Contact",{{"Date", type datetimezone}}),
     #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ContactID", "ContactNumber", "AccountNumber", "ContactStatus", "FirstName", "LastName", "EmailAddress", "SkypeUserName", "ContactPersons", "BankAccountDetails", "TaxNumber", "AccountsReceivableTaxType", "AccountsPayableTaxType", "Addresses", "Phones", "IsSupplier", "IsCustomer", "DefaultCurrency", "UpdatedDateUTC.1", "XeroNetworkKey", "SalesDefaultAccountCode", "PurchasesDefaultAccountCode", "SalesTrackingCategories", "PurchasesTrackingCategories", "PaymentTerms", "ContactGroups", "Website", "BrandingTheme", "BatchPayments", "Discount", "Balances", "HasAttachments.1", "Status", "LineAmountTypes", "LineItems", "TotalDiscount", "CurrencyCode", "CurrencyRate", "InvoiceID", "BrandingThemeID", "Url", "SentToContact", "ExpectedPaymentDate", "PlannedPaymentDate", "HasAttachments", "Payments", "CreditNotes", "Prepayments", "Overpayments"}),
     #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"DueDate", type datetimezone}, {"UpdatedDateUTC", type datetimezone}, {"FullyPaidOnDate", type datetimezone}}),
     #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Type"},INV_CR,{"Type"},"INV_CR",JoinKind.LeftOuter),
     #"Expanded INV_CR" = Table.ExpandTableColumn(#"Merged Queries", "INV_CR", {"INV_CR"}, {"INV_CR.1"}),
     #"Removed Columns1" = Table.RemoveColumns(#"Expanded INV_CR",{"Type"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"INV_CR.1", "INV_CR"}})
in
     #"Renamed Columns"

I added a INV_CR table to convert Type 0 to CR and 1 to INV.

This is a work in progress, my aim will be to load data to a CosmosDB, and get it all into the cloud. Feel free to get n touch if you have questions about this.

That Power Query should work nicely in Excel as well, so there are a few possibilities here

Focusing on Business Intelligence and Power BI

Much of my work over the last few years has focused on Data,Reporting and ETL, all components of Business Intelligence, BI. Now that Power BI is establishing itself as a BI market leader I find that I can re-focus my business more easily on BI and I intend to promote and market Power BI, and my abilities and experience in Power BI. I have been pursuing this in two areas, I am creating BI how-to videos on YouTube , and I have a new Mike Allen Systems web site to again focus purely on BI. Check out my videos and new web site, but most importantly check out Power BI , it’s a free download!