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.…
AuthorOz du Soleil
- 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
Skill Level Intermediate
1. What Is Get & Transform?
2. Working with Queries
3. Working with Columns
4. Working with Formulas
5. Pivoting and Unpivoting Data
Group data2m 31s
7. Appending a Query
8. Merging Data with Joins
9. Putting It All Together and Applying Your Learning
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.