Page 289 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

You also need to link the product ID to the product category but only the first 4 digits of the product code relate
to the product category. You create another column using the LEFT function to get the first 4 digits of the product
code, then use a VLOOKUP on this to get the product category. Now you can summarize the data by category.

Maybe it only takes an hour a month to do, but it’s pretty mindless work that’s not enjoyable and takes away from
time you can actually spend analyzing the data and producing meaningful insight.

With Power Query, this can all be automated down to a click of the refresh button on a monthly basis. All you need
to do is build the query once and reuse it, saving an hour of work each and every month!
Where is Power Query?
Power Query is available as an add-in to download and install for Excel 2010 and 2013 and will appear as a new
tab in the ribbon labelled Power Query. In 2016 it was renamed to Get & Transform and appears in the Data tab
without the need to install any add-in.
Importing Your Data with Power Query

Importing your data with Power Query is simple. Excel provides many common data connections that are
accessible from the Data tab and can be found from the Get Data command.
• Get data from a single file such as an Excel workbook, Text or CSV file, XML and JSON files. You can also
import multiple files from within a given folder.
• Get data from various databases such as SQL Server, Microsoft Access, Analysis Services, SQL Server
Analysis Server, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, Teradata and SAP HANA databases.
• Get data from Microsoft Azure
• Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.
• Get data from other sources like a table or range inside the current workbook, from the web, a Microsoft Query,
Hadoop, OData feed, ODBC and OLEDB.
• We can merge two queries together similar to joining two queries in SQL.

276

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77