Mike Allen's Blog

Doing Smarter Things With IT

Creating an Aging Calendar in Power Query

Getting the best out of your data in PowerBI often requires the creation of specialized calendars that help you analyze your data the that you need it. I’ve been creating calendars using Power Query. Another problem I’ve had is where to store simple reference data, like the aging buckets for an Aging Calendar. So I combined these two issues and this is my solution. I must emphasize that this is not the only one way of solving this problem and may not suit your requirements, but take my thoughts and see if you find them useful.

Here is the Power Query:

     AgingTable = #table(

     EndDate = Table.AddColumn(AgingTable, "EndDate", each Date.AddDays(DateTime.Date(DateTime.LocalNow()),-[start])),
     StartDate = Table.AddColumn(EndDate, "StartDate", each Date.AddDays(DateTime.Date(DateTime.LocalNow()), -[end])),
     Duration = Table.AddColumn(StartDate, "Duration", each Duration.Days([EndDate]-[StartDate])+1),
     Datelist = Table.AddColumn(Duration, "DateList", each List.Dates([StartDate],[Duration],#duration(1,0,0,0))),
     #"Expanded DateList" = Table.ExpandListColumn(Datelist, "DateList"),
     #"Removed Columns" = Table.RemoveColumns(#"Expanded DateList",{"start", "end", "EndDate", "StartDate", "Duration"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DateList", "Date"}})
     #"Renamed Columns"

This is fairly simple, but put together creates, I think, an elegant solution for an aging calendar. There’s an order column in the AgingTable, so that you can get your buckets in the right sequence on reports. I’ve started the date from today, but you can use other options, such a parameters. You can also insert this query into a PowerBI service DataFlow, which I’m starting to favor for my PowerBI calendars. Another nice technique here is the use of the list to generate the dates. Lists are one of the most powerful features of Power Query.

Let me know what you think, all feedback is greatly appreciated. Oh, and this advice is offered without any guarantee Smile

Information Ownership and Responsibility

All the information in a business system has an owner, what is interesting is that the owners of information may be unaware of its existence and their responsibilities as an owner. A computer system is a model of a business system, not a perfect model but a useful model. This is a generalization that applies to many types of model. Ownership and responsibility are implemented via business rules, which may be implemented in both code and data.

The owner of information may be the creator, the maintainer of even the reader of information. You will not find ownership by looking at the data or even at the code, it is intrinsic to a business system. The owner is responsible for the processes that affect information. What is even more interesting is that ownership is contextual and can vary over time.

Let’s dig a little deeper into the concept of ownership. An owner may be a person but is better described as an actor. An actor may be an entity like the accounts department, so that ultimate responsibility may lie with the manager of that department however ownership will be implemented by proxies such as accounts clerks and computer systems. As a piece of information travels through the business system , context changes, and ownership may change. For instance, as a customer transforms from a lead to an opportunity and then becomes a customer. So a piece of information can have multiple owners, but a sensible business rule would be for an instance of information to only have one ultimate owner for any given context. These actors should exist in a hierarchy with the ultimate owner delegating down the hierarchy of contextual actors and their proxies.

Why is this important? Well, information ownership is implicit in any business system and in order for a business system to function in a predictable fashion you need to know who is responsible for your data. Let's get back to customers, the core of any business. A business system is a model itself that will consist of several computer systems at various levels of integration. The problem is to identify a customer uniquely across all the systems that they are with. Duplicate (or more) customer records are the bane of any organization and ownership of customer identity is essential for managing customers. Duplicate records should be the exception, not the rule.

An example is often found in payment systems, businesses have multiple methods of making payments which often leads to multiple methods of generating account number. An account number and associated customer records should be raised by the information owner or their proxy. For those that would say this is impractical, I would say that this is what we have computer systems for. My favourite complaint is the use of cut and paste in business environments. Successfully implemented ownership will eliminate this.

Identification is the key to managing information ownership. Information ownership is the key to quality data. Quality data is key to high functioning computer systems and the resultant data analysis for decision management using BI and AI tools.

Analysis, design and improvement of business systems and the computer systems rely on establishing lean efficient information management. But let’s move on to the ownership of transactional data. Transactions frequently flow across multiple computer systems and multiple owners. An order is raised from a website entered into payment systems, general ledgers and order systems. At the end of the day we must be able to reassemble this information into unique transaction in order analyze sales performance.

This is a complex area that can yield high returns in the areas of business efficiency and planning, but again it requires identifying areas of responsibility. Then when dealing with business rules for tricky areas such as refunds and warranty, we will know which actors are involved.

When we know who owns the information we can also better identify the consumers of information, but I’ll leave that for another day.

Lean Information Governance

Good decision making is predicated on good information. A simple statement of the obvious. Who would want to run a business using poor quality decisions? This is all good in theory, what happens in practice is generally less than ideal. I’m going to treat that as a given.

When we proceed down the path of Business Intelligence(BI) and Artificial Intelligence(AI) we assume that our data is good enough and up to the task. Information is tricky stuff, sometimes we are not even aware of it until after the event that generated it has passed. Information also relies on the quality of the underlying data. Data can be defined succinctly, but information must be good enough for the task at hand.

Information governance is about ensuring that sources of information produce adequate and effective data of good quality. One of the principal issues of efficient data collection is knowing what data needs to be collected for an event or function that triggers the collection process. Suppose that you are recording the arrival of passengers at a train station, what data do you collect? Ticket number, destination, size, weight, how they arrived?

If the metrics of decision making are known the source data may be collected. Metrics are defined by combining source data which may be referential or transactional. Referential data is collected when the referential entity, such as a customer is identified for transactional purposes. Referential data, once collected must be maintained on a regular basis. Maintenance of referential data is one of the most important parts of data governance.

Maintaining referential data must be an ongoing task, some aspects can be usefully outsourced, such as credit ratings and corporate addresses. Personal details need to be kept up to date and meaningful, audit trails need to be kept. The concepts of master data need to be explored and responsibility and ownership of data need to be understood. This understanding needs to extend beyond business analysts and management. Data knowledge is an essential part of business culture, as relevant information is essential to the good operation of a business.

Transactional data is the lifeblood of any organization. Transactional data represents business activity and is the source of the fact tables that drive BI and AI. Transactional information is best collected as close to the source, in both time and location, as is possible. It is much harder to determine the essential data of a passenger at the destination than at the origin, purely because the start of a journey starts a transaction, whereas arrival at a destination implies a transaction in progress. Transactional data is inherently mutable, states and values change as the transaction is processed through the system. Process (or is that data?) analysis may indicate that we don’t need to record departures and arrivals as a single transaction. Then a change to the process, such a rewards system, makes it a necessity.

An excellent indicator of the utility of transactional data is how it is used. If it is used ubiquitously and is the one source of truth, it is very healthy data. If there are a variety of shadow systems that produce alternative versions of the truth, there will be issues with the data collections process. When the same information is collected multiple times (I’ve already been asked that a dozen times), that is a prime indicator of transactional torpor. In the same way, that reference data must have a master, transactional data must have a master and ownership. Transactional data often spawns subsidiary transactions in accounting ledgers and order books. These transactions must refer to the same reference data and this requirement must be handled in an appropriate fashion.

The key message here is that data is not separate to process, it is an intrinsic part of any process. Creators and consumers of business data need to have full awareness of what general and specific process rules exist in an organization. Another key concept is DRY (Don’t Repeat Yourself), collect information once. as efficiently as possible. Information Governance should be part of organizational culture. Executive responsibility for information governance must nurture that culture.

Being lean does not require a specific methodology, but lean information governance should be part of any continuous improvement effort. When you are faced with the latest flavour of business improvement, ask yourself if it incorporates better informational awareness into the process.

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


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:

     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"}})
     #"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!