Microsoft excel power query tutorial 2016 pdf

#Microsoft excel power query tutorial 2016 pdf code#

We can append a query to another query similar to a union of two queries in SQL.

We can merge two queries together similar to joining two queries in SQL.

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.

Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.

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.

You can also import multiple files from within a given folder.

Get data from a single file such as an Excel workbook, Text or CSV file, XML and JSON files.

Excel provides many common data connections that are accessible from the Data tab and can be found from the Get Data command. Importing your data with Power Query is simple. 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. Now you can summarize the data by 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.

#Microsoft excel power query tutorial 2016 pdf code#

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. Then you need to summarize the sales by salesperson and calculate the commission to pay out. The system only outputs the sales personвЂ™s ID, so you need to add a new column to the data and use a VLOOKUP to get the salesperson associated with each ID.

You then use the text to column feature to split out the data into new columns. Imagine you get a sales report in a text file from your system on a monthly basis that looks like this.Д®very month you need to go to the folder where the file is uploaded and open the file and copy the contents into Excel.