It is not surprising that these four business intelligence products have an offering in Office 365<\/a>. I really like office 365 as a platform to share the final analysis with your team, department and\/or company. Like any data in the cloud, caution should be taken when it is confidential information that is governed by auditing rules like HIPAA<\/a> or such.<\/p>\n Today, I will start talking about Power Query which is a self service ETL tool. This tool was released <\/a>in August of 2013. The online help<\/a> is a good place to start learning about this tool.<\/p>\n Because these tools are relatively new, I found out that once in a while the add-in will freeze. I suggest using a virtual machine so that rebooting the operating system does not cause you real down time. I am hoping that these tools mature over time and this annoyance will go away.<\/p>\n The first step in using Power Query is to download the add-in here<\/a> from the Microsoft site. Please read the system requirements. This version requires the Dot Net Framework 3.5. You might have to install this first as a prerequisite. Next, run the setup program as shown here.<\/p>\n The Power Query tool is built to grab data from various sources and mash it up in MS Excel. <\/p>\n The data can be stored as either a worksheet with a million row limit. Or in the data model that is managed by the xVelocity in-memory analytic engine. This engine uses a column store philosophy that does very well with non-unique data. It can produce some high compression ratios.<\/p>\n Okay, enough about the tool. Let’s get down to business by extracting, translating and loading (ETL) data into Microsoft Excel.<\/p>\n The get external data section of the ribbon is where we will start. <\/p>\n I know there is a data set in Wikipedia that has abbreviations for US states in a United States Postal Service (USPS) format. I want to add this data to my model for analysis.<\/p>\n By clicking the on-line search button, I can enter the key words to find this public data set. <\/p>\n Choose the edit option to start translating the data.<\/p>\n The Query editor allows you to apply translations to the source data. Each step is saved so that it can be replayed during a refresh (re-import). Please note, the load settings determine the ultimate destination for the data.<\/p>\n Here are some of the translations I performed to cleanup the USPS data.<\/p>\n Just like many tools, the Power Query is built upon a language. Clicking the advanced editor brings up the Power Query formula that matches the saved steps.<\/p>\n At the end of the process, we want the NAME, TYPE, ANSI and USPS columns for each state. The workbook query should be named “Postal Codes” and the worksheet should be named “State List”. Please note, we have eleven island like locations in the data for a total of 69 rows.<\/p>\n We can edit the saved query and filter out these rows. The resulting data set has 59 rows.<\/p>\n![\"A01-power-query-install-requires-dot-net-3.5\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A01-power-query-install-requires-dot-net-3.5.jpg\")
<\/a><\/p>\n![\"A02-power-query-get-external-data\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A02-power-query-get-external-data.jpg\")
<\/a><\/p>\n![\"A03-on-line-wikipedia-information\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A03-on-line-wikipedia-information-1024x533.jpg\")
<\/a><\/p>\n![\"A04-search-online-wikipedia-dataset-usps-states\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A04-search-online-wikipedia-dataset-usps-states-1024x781.jpg\")
<\/a><\/p>\n![\"A05-power-query-sort-filter-remove-columns-rename-columns\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A05-power-query-sort-filter-remove-columns-rename-columns.jpg\")
<\/a><\/p>\n\n
![\"A06-power-query-advanced-editor-internal-language\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A06-power-query-advanced-editor-internal-language-1024x671.jpg\")
<\/a><\/p>\n![\"A07-power-query-saved-script-for-later-use\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A07-power-query-saved-script-for-later-use-1024x611.jpg\")
<\/a><\/p>\n
![\"A08-power-query-update-save-n-close-less-data\"](\"https:\/\/craftydba.com\/wp-content\/uploads\/2014\/03\/A08-power-query-update-save-n-close-less-data-1024x612.jpg\")
<\/a><\/p>\n