Part 2 of a 5 Part Series
What is Power Query? Power Query is a self-service data extraction tool that is a free add-in for Excel 2010 or higher. It allows users that are already comfortable with Excel a smaller learning curve to start enjoying Power Query. Power Query has a vast array of options that it can use as data sources. The types of sources that can be used are: Online Search; From Web; From File; From Database’ From Other Sources; From Table; and each categories subdivisions.
Extracting a Source Data One of the key tenets of Self-Service BI is that business users should be able to get the data they need to gain insights into their businesses in a timely manner. With Power Query, business users who use Excel can immediately begin extracting, transforming, and combining disparate sets of source data into meaningful information and knowledge to help them gain important insights about their businesses. To connect to a SQL data source follow this steps:
1. Click on the Power Query in Excel and then on the “From Database” button and select From SQL Server Database.
2. Supply SQL Server and database connection and then select the table(s) would want to work with from the Navigator pane.
3. Select Patron database and then “Edit” button.
Shaping your Data Loading source data is only part of the puzzle, because in most cases your source data needs to be transformed or shaped differently to make it consumable. Typical transformation tasks include but are not limited to formatting record keys from different data sources into a single format; splitting data that is combined in a single column into multiple columns; and decoding cryptic column names using dictionary files to give the column values meaning to business users.
1. Transform/cleanse data by clicking replace values and filling in find and replace data.
2. Click on Split Column and select Custom delimiter from drop down and “-” to split the Zip Column.
3. Replace Values to clean up values in a selected column
A. In the query Editor ribbon, click replace values or right-click the column and click Replace Values to replace the data.
4. Naming the query
A. In the Query Setting pane, in the Name text box, enter your name to define the query. This is also where you can modify the applied steps to the original data.
5. After you have cleansed, load the query to a worksheet
A. In the Query Editor Ribbon, in the Query Group, click Apply & Close.
Power Query for Excel almost effortlessly handles most data transformation tasks and quickly makes your data consumable. What’s especially powerful is the way that Power Query implements its data transformation functionality. For every change you make to a dataset, from simple tasks such as renaming columns or replacing values in strings, to traditionally more complex tasks such as splitting a single column into multiple columns or un-pivoting a large number of columns with cryptic names so they can be decoded, a macro is automatically created and executed to implement the change. If you make a mistake and want to undo a change, you simply delete the macro from the query, and the change will be undone.
Power Query is an excellent tool in Microsoft’s Power BI Suite. It can be considered the heavyweight champ when extracting and shaping the data that business users need in a timely manner for additional analysis and visualization.
For more information on Power BI or Power Query, contact KTL Solutions at 866.960.0001 or email Barry at Sales@ktlsolutions.com . Stay tuned for Barry’s Part 3 of his Microsoft Power BI 5 Part Series.
