Transforming data in Excel with Power Query

- [Instructor] We've been tasked to look at how long it takes for our supplier transactions to go from the transaction date, to the finalization date. We want to see if there's any suppliers that may take a little bit longer for any given transaction. What we really hope to find, is that most of our transactions are under three days. We have all the data we need, but we don't have all the calculations we need to perform the analysis. So let's get started with a few transformations, and building out the calculations we need. Okay, I'll go to queries and connections, and I'll choose to edit my supplier's query. There are few transformations that, normally would've caused me to create functions in Excel, but because I'm using Power Query, I can perform these functions without having to create a function. Let me start by showing you supplier name. For our purposes, we need all the supplier names to be in uppercase. I can easily transform this column to uppercase. Also, we need the transaction date, but we also need the transaction year. I'll right-click transaction date, I'll duplicate that column, and then I'll transform this to just show the year. I can do that by right-clicking, transform, I can choose year, and then choose year. Okay, I'll go ahead and name that, transaction year. And then I'll just go ahead, and move it over by my transaction date. I have two amounts here. I have the amount excluding tax, and the actual tax amount. What I really need is the total amount. So I'm going to create my first formula. I'll go to add column, I'll choose custom column, I'll name it total amount. And then using my available columns on the right hand side, I'll scroll, I'll double-click amount, excluding tax, I'll add the plus sign, I'll double-click tax amount. It tells me that I have no syntax errors, and I can click OK. I'll go ahead and adjust this to be a currency data type. I only need the total amount, so I'll go ahead and right-click amount excluding tax, and choose remove. And then I can also remove my tax amount. Now we want to look at the number of days that have elapsed between the transaction date, and the finalization date. Let's go add another column. I'll go to custom column, I'll name this days, I'll choose transaction date, minus, finalization date, and click OK. Using this method will return the number of days, but because the transaction date was before the finalization date, it's showing as a negative number. It also doesn't really look like a number. It looks like a timestamp. What I'll do is go ahead and change it to a whole number. And what I'm really looking for is the absolute value. So again, I'll right-click, transform, and choose absolute value. Now I have all of the information I need, except I don't have the field that tells me if it's over or under three days. I'll use a conditional column. I'll tell it to look at the days, and then provide me text, that says over three days, or under. I'll go to conditional column, I'll name this over under, I'll choose days. And if it's greater than, or equal to, three days, I want it to say three days or more. For anything that's two days or less, I want it to say two days or less. This is a logical function that looks at the days, and then gives me a value if it's true, or a value of false. If I were doing this in Excel, it's similar to an IF function. I'll go ahead and click OK. Now I'm prepared to start my analysis. I'll go to home, I'll choose close and load. Now I see I have all of my extra columns that I've added, and my supplier name is automatically capitalized. This is fantastic. I'm ready to start looking at my supplier transactions, to determine if they're over or under three days. Now that our data is prepared, we can answer a few common questions on the production days. We'll start by inserting a pivot. I'll do insert, pivot table. It's going to use my supplier's range, and it'll be on a new worksheet. Perfect. I'll drag my over and under to rows, I'll go ahead and drag my supplier transaction ID to values. And because it's a number, it will automatically sum it. I'll go ahead and change that to account. Click OK. Just looking at the numbers, I can tell that most of the transactions have been three days or more. Let me do one more quick analysis step. I can right-click, show, value as, and tell it to show me the percentage of the grand total. This high level detail tells us that 69% of our transactions, really are taking three days or more to produce, only 31% approximately, are actually under two days. Okay. We need to do some more analysis. Transforming data can mean a lot of different small techniques applied to the data as you work to get to your analysis.