Project Botticelli

Power Query Fundamentals Purchase this course

26 November 2014 · 3 comments · 2787 views

Chris Webb explains Power Query query editing steps

Let Chris Webb introduce the fundamentals of Power Query: connecting, transforming, and loading of the results, in this detailed, hands-on 45-minute video, which focuses on the Query Editor in Power Query.

Power Query can connect to many types of data source: relational databases, file formats such as CSV, XML, JSON, and Excel, web pages, REST-based web services (with special support for ODATA), and many more such as: Active Directory, Azure, Exchange, Facebook, SalesForce, and SAP Business Objects. You can even connect to a folder and combine data from multiple CSV files into a single resultset. The Online Search functionality allows you to search for public data curated by Microsoft, such as data from Wikipedia, Quandl, World Bank and Azure Data Market, or find data that has been shared via the Power BI Data Catalog within your organisation.

Once you have extracted data from a data source there are many ways you can transform it. You can rename, delete and move columns, you can replace null values with the last or the next non-null value in the column, you can split a text column up into multiple columns by a delimiter such as a space or a comma, you can aggregate data in a similar way to a GROUP BY query in SQL, you can pivot, unpivot and transpose data, you can sum or multiply values across multiple numeric columns, and you can create your own calculated columns using Power Query own expression language, called M.

Power Query can load data to a table in an Excel worksheet and/or to a table in the Excel Data Model, which you can further manipulate and enrich using Power Pivot and DAX, or indeed it does not need to load the data anywhere at all. Queries can also be used as a data source for other queries. Two queries can be joined on one or more columns, just like a join between two tables in SQL—this is called merging. You can also append the output of one query onto the end of another—this is called combining.

To make the most out of this tutorial, consider following Chris’s demos on your own, starting and stopping the video as you practice.

Log in or purchase access below to the premium version of this content.

What is Power Query?
What is Microsoft Power View?

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

Comments

amg · 24 March 2015

Hello Chris
I am not sure if Power Query or Power Pivot is the right place do get what I need and would love some guidance. I need to expand a date range and multiply a price by the interval represented by start and end dates. My data looks like this;
Price Start End
1177 1/1/2014 12/31/2014
496 3/1/2014 12/31/2014
675 3/1/2014 12/31/2014
1154 1/1/2013 12/31/2013
1556 1/1/2013 12/31/2013

So that I need 12 * 1556 for example. Is this easily done natively or do I need some kind of date function to get there?
Thanks a lot

amg · 24 March 2015

Hi Chris
This is a mind blowing demo. I think I solved my prior issue by fooling around in the tool. What I did was split the column on the slashes to expose the month number in its own column, then, subtracted the start month from the end months, which gave me the number of months, and multiplied it by the price. Not elegant (or home grown M), but it got me home ;-)
One thing i forgot to ask is that at 34:44 during the Append demo, the April data looks to have the same structure in the CSV file as in the query, but when it is appended, the month name is in the fruit column! how come?
Thanks again. I really appreciate the lessons.

Chris Webb · 25 March 2015

Glad to hear you've solved your problem! Re the Append demo, I think that's just a bug in my data - there's nothing wrong with Power Query. Well spotted!