Number-specific query editing tools

Keyboard Shortcuts

In this video, review Power Query tools designed specifically to work with numbers or data fields, such as returning aggregated values like sums or averages, creating new calculated columns, rounding numbers, and so on.

- [Instructor] Okay, moving right along…to the number specific tools,…taking a look at the Transform tab…in the Query Editor.…Towards the end there you'll see this…grouping of tools.…These are our number tools.…And we've got some interesting options in here.…First, we've got Statics options.…And these are aggregation style functions,…like sum, minimum, maximum, average,…count values, count distinct.…And what's interesting about these is they…evaluate these functions for an entire selected column…and return a single value.…

So, you can't use them to create a new column…and you don't evaluate on a row-by-row basis.…So, as you can probably guess,…this isn't really helpful when it comes to…shaping or transforming data…or prepping it for loading.…But it is helpful to explore and better understand…the data that you're working with.…Next up, we've got Standard Options.…So these are other statistical operations and calculations.…You've got the basic standard ones,…like addition, multiplication, subtract, divide, etc.…

And then you've got some pretty crazy advanced stuff…

Resume Transcript Auto-Scroll

Author

Released

5/14/2018

Microsoft Excel includes a powerful feature called Power Query—also known as Get & Transform or Get Data—which provides fast and powerful data gathering and cleansing capabilities. In this course—the first installment in the Excel Business Intelligence series—follow along with experienced Excel trainer Chris Dutton as he shows you the robust capabilities of Power Query. Chris kicks off the course by outlining the power Excel landscape and spelling out when to use business intelligence tools like Power Query, Power Pivot, and DAX. He then dives into Power Query, explaining how to leverage key query editing tools to connect and transform data. Learn about basic Power Query table transformations, text-specific query editing tools, how to merge and append queries, and more. Chris also shares best practices for working efficiently with Power Query.