Microsoft Power Query For Excel For Mac

Jul 15, 2019 Testing Power Query in Excel for Mac. My first intention was to figure out what M functions are already available inside of this integration. You can see the full list of available functions on Power Query in Excel for Mac from here. Most of the functions are already in there. Quickly get & transform your data for analysis in a few simple steps using Power Query in Microsoft Excel. Join MVP Miguel Escobar as he not only guides us through the steps to get & transform data for analysis but also introduces us to Power Query in Excel for Mac. Miguel Escobar is a renown.

Award-winning instructors

Get certified

Mobile (learn on-the-go)

Regularly updated content

So you’ve impressed your coworkers with your encyclopedic memory of formulae, become a Pivot pro, and started working IF statements into regular conversation. Now what?

Ken Puls, our award-winning instructor, says Power Query. In a world where data is king, you should know how to work with and understand it – and this tool helps you do just that.

In this Power Query online course, you will learn the BI (Business Intelligence) process of importing data, appending and merging tables, conditional logic, data transformation, and organization. The course starts with the basics of Power Query, and works its way up. This means that you’ll be a pro in no time, even if you’re a beginner with the tool.

If you spend a lot of time manually preparing your data for analysis, this Excel Power Query training will teach you how to shave hours off your day with practical examples, tips, and tricks. Knowledge of Pivot Tables is an asset, but not a requirement. By the end of the course, you will be an expert at cleaning, transforming and reshaping your data so that it is ready to analyze.

Fun fact: the Power Query add-in was so useful, Microsoft built it into Excel 2016 (you can find it in the Data tab under the Get & Transform section).

Highlights:

  • 33 practical tutorials
  • Review the process used to develop and update modern business intelligence solutions in Excel
  • Learn how to consolidate data from databases and external Excel files
  • Explore the seven ways to merge data
  • Build your own conditional formulae
  • Learn how to pivot and unpivot your data
  • Use tools to audit your modules

Video tutorials are recorded in Microsoft Excel 2016 for PC. Learn more about how a GoSkills Excel certification can boost your career.

If you’re interested in Power Query, we also recommend the Power BI,Dashboards, and Pivot Tables courses to build your reporting skills.

Want to be a more efficient Excel user? Start learning 200 of the best Excel shortcuts for PC and Mac.

Once enrolled, our friendly support team and tutors are here to help with any course related inquiries.

Auto
  • 720p
  • 540p
  • 360p
cc

Certificate

Yes (learn more)

Accredited by

CPD

Video duration

3h 24m

What is Power Query and What Does it Do?

Looking at what Power Query does, and why this technology will change your life and the way you look at data.

Acquiring and Updating Power Query

In this lesson we will look at how to acquire the Power Query software, and give you important information about the update cycle.

Overview of the BI Process

A review of the process used to develop and update modern business intelligence solutions in Excel.

Getting Basic Data

An introduction into using Power Query technology to get data for an Excel business intelligence solution.

Creating PivotTables

The whole reason we get data in the first place is to turn it into information. And the best tool to quickly turn data into information? Excel's PivotTable!

The Value is in the Refresh

While there is certainly value in the initial analysis, the real value in any BI solution comes from the refresh. When your boss comes to you and asks you to update the solution it took you hours to build and gives you a deadline of 10 minutes, you need to be able to say 'Yes, I've got this.'

Understanding Dates and Locale

One of the things we need to understand is how Excel interprets dates, and how we can override the defaults by setting the data's Locale.

Getting Data from Text Files

Importing data from a text file into an Excel table.

Getting Data from Excel Tables

Data often lives inside Excel tables. But even if it doesn't, Power Query will create one for you.

Getting Data from Excel Ranges

Sometimes you don't want to add a table style to your document. In this lesson we'll look at how to import data from an Excel range.

Getting Data from External Excel Files

Sometimes you want to build a solution that pulls its data from a different Excel workbook. In this lesson we will show you how.

Getting Data from Databases

One of the most important data sources to pull from is a database, but they have their own interesting nuances as well. In this lesson we will take a look at those.

The following table provides the action and the shortcut key command to perform each action. You save a lot of time using the shortcut key operations because you are not spending time searching the file or quick menus for these editing options. File OperationsSome of the most common operations you do in Word include opening, closing, saving, and printing. ActionKey CommandTo open a file command + OTo close a file command + WTo save a file command + STo print a file command + PTo open a new document command + NEdit OperationsEditing operations such as cut, copy, paste, and check spelling are also used frequently. Microsoft office 2003 mac Because these operations are repeated often, using the keyboard shortcuts can simplify and speed up the process.

Appending Tables Manually

Appending tables is the process of stacking them vertically to create long tables that can be used to feed business intelligence solutions and PivotTables.

Append All Files in a Folder - Theory

Power Query adds the ability to reach into a folder and append all files in the folder based on the transformations of a single file. Before you can leverage it, however, you need to learn the background, and that's what this lesson is all about.

Append All Files in a Folder - Preparation

Before you unleash this magic skill, there is a bit of prep-work that should be done to future-proof your solution and make it more portable. In this lesson we will explore how to do this.

Append All Files in a Folder - Application

This lesson will teach you how to unleash the magic of appending all files in a folder, and show you how effective it is.

Append Objects Within the Current Excel Workbook

Wouldn't it be nice if you could append all Tables in the current Excel workbook without having to do each manually? Microsoft office 2010 for mac 10.6.8 free download full version. You absolutely can - and this lesson shows how to avoid causing massive errors in the process.

Append Objects in an External Excel Workbook

This lesson explores connecting to an external workbook to append all tables, worksheets and/or ranges within it.

7 Ways to Merge Your Data

In this lesson we will explore seven different ways to find matching records, all without using a single VLOOKUP!

Basic (Left Outer) Joins

The lesson demonstrates how to use Power Query to replicate VLOOKUP's exact match functionality (without writing any VLOOKUPs!)

Outer and Inner Joins

Since there are so many joins, it really pays to explore them all. In this lesson we will look at the Left Outer, Right Outer, Full Outer and Inner joins, as well as show a trick for joining on composite keys.

Anti Joins

Some of the most powerful joins for those trying to perform reconciliations are those that identify the items that do NOT match. In this case we will explore how the Left Anti, Right Anti and Full Anti joins can be used to accomplish this.

Basic Conditional Logic

No data manipulation tool would be complete without the ability to use conditional logic. In this lesson we will explore the benefits of Power Query's user interface driven 'Conditional Column' tool.

Writing Your Own Conditional Formulae

Unfortunately, not every conditional logic scenario can be created using the 'Conditional Column' tool. In this lesson you'll learn about the shortcomings of this tool and how to work around them by using the 'Add Custom Column' button.

Solving Logic Problems Using Errors

This lesson shows how we can use errors to our benefit to solve conditional logic problems.

Create Columns from Examples

Sometimes trying to extract data from a column can be difficult, so why not let Power Query help? Microsoft has put significant effort into a tool called 'Columns From Examples' where you can define and refine the output, and let Power Query build the formula to get the results.

Unpivoting Data

If you work with PivotTables a lot, you know that they put information out the way users want to read it, but you can't use it to drive another PivotTable. So what if there was an easy way to unpivot the data? There is, and it's called Power Query!

Unpivoting Subcategorized Data

The challenge with the Unpivot functionality is that you can only unpivot based on a single header row. So what if you have multiple rows of subheaders? In this lesson we will show you how to circumvent that issue.

Pivoting Data

While we can use PivotTables to pivot data, occasionally we need to pivot our data in the data preparation phase. This lesson exposes how the Pivot functionality works in Power Query.

Pivoting Stacked Data

This lesson exposes a very important pattern for wrangling data. Once you can get it into a single column, hit it with this recipe and it will land nicely into a table.

Grouping Data

When building models, many users pull in data that is far too granular, resulting a many more records than they need. Power Query offers a tool to group that data for you, so that you aren't wasting storage space on unnecessary records.

Grouping Queries

This lesson shows how to create query groups so that you can keep your queries organized. Super useful for creating a self-documenting query chain.

Viewing Query Dependencies

If you ever need to audit your models, you'll find this tool can be helpful. It shows how the queries link together and flow from one to the other.

Award-winning instructors

Get certified

Mobile (learn on-the-go)

Regularly updated content

So you’ve impressed your coworkers with your encyclopedic memory of formulae, become a Pivot pro, and started working IF statements into regular conversation. Now what?

Ken Puls, our award-winning instructor, says Power Query. In a world where data is king, you should know how to work with and understand it – and this tool helps you do just that.

In this Power Query online course, you will learn the BI (Business Intelligence) process of importing data, appending and merging tables, conditional logic, data transformation, and organization. The course starts with the basics of Power Query, and works its way up. This means that you’ll be a pro in no time, even if you’re a beginner with the tool.

If you spend a lot of time manually preparing your data for analysis, this Excel Power Query training will teach you how to shave hours off your day with practical examples, tips, and tricks. Knowledge of Pivot Tables is an asset, but not a requirement. By the end of the course, you will be an expert at cleaning, transforming and reshaping your data so that it is ready to analyze.

Fun fact: the Power Query add-in was so useful, Microsoft built it into Excel 2016 (you can find it in the Data tab under the Get & Transform section).

Highlights:

  • 33 practical tutorials
  • Review the process used to develop and update modern business intelligence solutions in Excel
  • Learn how to consolidate data from databases and external Excel files
  • Explore the seven ways to merge data
  • Build your own conditional formulae
  • Learn how to pivot and unpivot your data
  • Use tools to audit your modules

Video tutorials are recorded in Microsoft Excel 2016 for PC. Learn more about how a GoSkills Excel certification can boost your career.

If you’re interested in Power Query, we also recommend the Power BI,Dashboards, and Pivot Tables courses to build your reporting skills.

Want to be a more efficient Excel user? Start learning 200 of the best Excel shortcuts for PC and Mac.

Once enrolled, our friendly support team and tutors are here to help with any course related inquiries.

Auto
  • 720p
  • 540p
  • 360p
cc

Microsoft Power Query Para Excel Mac

Certificate

Yes (learn more)

Accredited by

CPD

Video duration

3h 24m

What is Power Query and What Does it Do?

Looking at what Power Query does, and why this technology will change your life and the way you look at data.

Acquiring and Updating Power Query

In this lesson we will look at how to acquire the Power Query software, and give you important information about the update cycle.

Overview of the BI Process

A review of the process used to develop and update modern business intelligence solutions in Excel.

Getting Basic Data

An introduction into using Power Query technology to get data for an Excel business intelligence solution.

Microsoft Power Query For Excel For Mac 2016

Creating PivotTables

The whole reason we get data in the first place is to turn it into information. And the best tool to quickly turn data into information? Excel's PivotTable!

The Value is in the Refresh

While there is certainly value in the initial analysis, the real value in any BI solution comes from the refresh. When your boss comes to you and asks you to update the solution it took you hours to build and gives you a deadline of 10 minutes, you need to be able to say 'Yes, I've got this.'

Understanding Dates and Locale

One of the things we need to understand is how Excel interprets dates, and how we can override the defaults by setting the data's Locale.

Getting Data from Text Files

Importing data from a text file into an Excel table.

Getting Data from Excel Tables

Data often lives inside Excel tables. But even if it doesn't, Power Query will create one for you.

Getting Data from Excel Ranges

Sometimes you don't want to add a table style to your document. In this lesson we'll look at how to import data from an Excel range.

Getting Data from External Excel Files

Sometimes you want to build a solution that pulls its data from a different Excel workbook. In this lesson we will show you how.

Getting Data from Databases

One of the most important data sources to pull from is a database, but they have their own interesting nuances as well. In this lesson we will take a look at those.

Appending Tables Manually

Appending tables is the process of stacking them vertically to create long tables that can be used to feed business intelligence solutions and PivotTables.

Append All Files in a Folder - Theory

2016 Power Query

Power Query adds the ability to reach into a folder and append all files in the folder based on the transformations of a single file. Before you can leverage it, however, you need to learn the background, and that's what this lesson is all about.

Append All Files in a Folder - Preparation

Before you unleash this magic skill, there is a bit of prep-work that should be done to future-proof your solution and make it more portable. In this lesson we will explore how to do this.

Append All Files in a Folder - Application

This lesson will teach you how to unleash the magic of appending all files in a folder, and show you how effective it is.

Append Objects Within the Current Excel Workbook

Wouldn't it be nice if you could append all Tables in the current Excel workbook without having to do each manually? You absolutely can - and this lesson shows how to avoid causing massive errors in the process.

Append Objects in an External Excel Workbook

This lesson explores connecting to an external workbook to append all tables, worksheets and/or ranges within it.

7 Ways to Merge Your Data

In this lesson we will explore seven different ways to find matching records, all without using a single VLOOKUP!

Basic (Left Outer) Joins

The lesson demonstrates how to use Power Query to replicate VLOOKUP's exact match functionality (without writing any VLOOKUPs!)

Outer and Inner Joins

Power Query Excel 2010

Since there are so many joins, it really pays to explore them all. In this lesson we will look at the Left Outer, Right Outer, Full Outer and Inner joins, as well as show a trick for joining on composite keys.

Anti Joins

Microsoft power query for excel for mac free

Some of the most powerful joins for those trying to perform reconciliations are those that identify the items that do NOT match. In this case we will explore how the Left Anti, Right Anti and Full Anti joins can be used to accomplish this.

Basic Conditional Logic

No data manipulation tool would be complete without the ability to use conditional logic. In this lesson we will explore the benefits of Power Query's user interface driven 'Conditional Column' tool.

Writing Your Own Conditional Formulae

Unfortunately, not every conditional logic scenario can be created using the 'Conditional Column' tool. In this lesson you'll learn about the shortcomings of this tool and how to work around them by using the 'Add Custom Column' button.

Solving Logic Problems Using Errors

This lesson shows how we can use errors to our benefit to solve conditional logic problems.

Create Columns from Examples

Sometimes trying to extract data from a column can be difficult, so why not let Power Query help? Microsoft has put significant effort into a tool called 'Columns From Examples' where you can define and refine the output, and let Power Query build the formula to get the results.

Unpivoting Data

If you work with PivotTables a lot, you know that they put information out the way users want to read it, but you can't use it to drive another PivotTable. So what if there was an easy way to unpivot the data? There is, and it's called Power Query!

Unpivoting Subcategorized Data

The challenge with the Unpivot functionality is that you can only unpivot based on a single header row. So what if you have multiple rows of subheaders? In this lesson we will show you how to circumvent that issue.

Pivoting Data

While we can use PivotTables to pivot data, occasionally we need to pivot our data in the data preparation phase. This lesson exposes how the Pivot functionality works in Power Query.

Pivoting Stacked Data

This lesson exposes a very important pattern for wrangling data. Once you can get it into a single column, hit it with this recipe and it will land nicely into a table.

Grouping Data

When building models, many users pull in data that is far too granular, resulting a many more records than they need. Power Query offers a tool to group that data for you, so that you aren't wasting storage space on unnecessary records.

Grouping Queries

This lesson shows how to create query groups so that you can keep your queries organized. Super useful for creating a self-documenting query chain.

Viewing Query Dependencies

If you ever need to audit your models, you'll find this tool can be helpful. It shows how the queries link together and flow from one to the other.