{"id":7169,"date":"2014-03-07T00:00:06","date_gmt":"2014-03-07T00:00:06","guid":{"rendered":"http:\/\/craftydba.com\/?p=7169"},"modified":"2016-04-19T20:29:04","modified_gmt":"2016-04-19T20:29:04","slug":"power-query-part-4","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=7169","title":{"rendered":"Power Query \u2013 Part 4"},"content":{"rendered":"

There has been a big push for self service business intelligence tools by Microsoft in recent months. The center of this new universe is Microsoft Excel. <\/p>\n

I will carry on with talks about Power Query which is a self service ETL add-in for MS Excel. This tool was released <\/a>in August of 2013. In addition to my blog, the online help<\/a> is a good place to start learning about this tool.<\/p>\n

Last time, I demonstrated how the “reduce” section of ribbon has buttons that can refine the number of rows and columns in the source data set. Today, I am going to start covering the “sort” and “transform” sections of the ribbon. <\/p>\n

\"F01-power-query-toolbar-ribbon\"<\/a><\/p>\n

Again, I will be working with the US state abbreviations<\/a> data set from Wikipedia. <\/p>\n

The “sort” operation allows you to order the data in ascending or descending order. The picture below shows states in descending order of abbreviation.<\/p>\n

\"F02-power-query-transform~sort-data\"<\/a><\/p>\n

Many times the data we get for analysis is not in the correct format. The core MS Excel application has a “text to columns” operation to parse data that might be in a fixed width or delimited format. Power Query has the “split column” operation instead.<\/p>\n

One way to split columns is on fixed length (position). <\/p>\n

\"F03-power-query-transform~split-column-by-position\"<\/a><\/p>\n

I see this option less flexible than the one built into the core product that allows you to place arrows where divisions occur in a sample preview of the data. In real life, a fixed length file will not have columns with the same number of characters.<\/p>\n

The image below shows the core product converting fixed length data from the [Employee] table in the [HumanResources] schema in the [AdventureWorks2012] database into columns.<\/p>\n

\"F03-power-query-transform~text-to-columns\"<\/a><\/p>\n

Another way to split columns is on a known character (delimiter). A very common format is comma separated values (CSV) shown below. <\/p>\n

\"F04-power-query-transform~split-column-by-delimited\"<\/a><\/p>\n

Sometimes we get data for analysis that needs to be aggregated before being used. That is where the “group by” operation comes to play.<\/p>\n

For instance, our US state abbreviations data set can be categorized by type. We might be interested in the number of mail parcels that go out to the islands.<\/p>\n

First, the raw data has a type column that we can group by.<\/p>\n

\"F05-power-query-transform~data-before-group-by\"<\/a><\/p>\n

Second, we need to decide on what aggregation function we want to use. All the standard SQL functions are available: count, avg, sum, min, and max.<\/p>\n

\"F05-power-query-transform~group-by-choices\"<\/a><\/p>\n

Third, applying the group by operation to our data produces our expected result.<\/p>\n

\"power-query-state-data-before-group-by-3\"<\/a><\/p>\n

Summing-up this article, there are many operations in the “transform” section of the ribbon that allow you to mold your data into a format that you want. Next time, we will finish exploring the rest of the operators in this section.<\/p>\n","protected":false},"excerpt":{"rendered":"

There has been a big push for self service business intelligence tools by Microsoft in recent months. The center of this new universe is Microsoft Excel. I will carry on with talks about Power Query which is a self service ETL add-in for MS Excel. This tool was released in August of 2013. In addition to my blog, the online help is a good place to start learning about this tool. Last time, I demonstrated how the “reduce” section of ribbon has buttons that can refine the number of rows…<\/p>\n","protected":false},"author":1,"featured_media":7070,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1042],"tags":[93,15,1043,1045,811,1063],"class_list":["post-7169","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-power-query","tag-group-by","tag-john-f-miner-iii","tag-power-query","tag-self-service-etl","tag-sort","tag-split-column"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/7169","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7169"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/7169\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/7070"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}