Oz du Soleil shows how to write formulas in Get & Transform in Excel 2016 using custom columns. This example shows how to fix the problem with leading zeroes that disappear—for example, when 0017 shows up as 17, how do you force Get & Transform to display
- Yes, it's possible to write formulas in Get & Transform, and we're going to look at that now. Writing formulas in Get & Transform is very different from doing it in native Excel. Lots of differences. First of all, you can see a list of the available functions at this hyperlink that's being provided in the workbook. Why do I send you to a website? Because in Get & Transform, as you'll see, we don't get any tips on what's available.
If I did this in Excel, equals a it's giving me a list of what's available to me. I say, "Okay, maybe I want AND, "or maybe I want absolute value," and then if I select absolute value, it's giving me a sense of the syntax that'll make it work. If I go to AND, it's telling me what it does and what it wants.
We aren't going to see that here pretty soon. So enough talking and let's start doing. Here, I've got some data and the phone extensions, they should be four characters, but grading look at it, it's only two because the number is actually zero, zero, one, seven, and grading is lower case. So we've got to fix two things here. Get our leading zeros back and make all of the departments capital. Going to use functions in Get & Transform.
Let's get at it. Selected a dataset, go to Data, From Table. Here's the data. First, let's fix the Department. We're going to do that with a custom column. Add, Custom Column. =Text.Upper open parentheses, double-click Department, close parentheses.
Okay. Everything is capitalized. Let's get rid of this column and move this over. Beautiful. Now let's deal with the leading zeros. Add a Custom Column. Call this new column EXT Let me do this and then I'll explain it. Text.PadStart, open parentheses, Text.From, open parentheses, double-click Phone, close parentheses, comma.
Okay, stop right here. First we have to take the numbers that comprise the Phone data and convert it into text. That's the Text.From. PadStart is what we're going to use to make sure we have four characters, and that's where this four is going to be. Comma. And what do we want to fill in with for those entries that are three, two, or one characters long? We want to put a zero.
Closed parentheses. Now notice I got no syntax help, but there's this No syntax errors have been detected message down here in the bottom. Click okay. Now we've got what we want, and also notice the formula bar. We can modify our formula here. So if we go to four, change that to a five, and we can even change this zero to a Y if we want. Okay.
And want to change that back. This is what we want. Let's remove this column. And load this to the workbook. Want to load it next to the source data. Great, there it is, and we have more data to add. We just refresh.
Beautiful. Now, there is your first taste of working with formulas in Get & Transform.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins