Excel

Merge Queries Often, ERP systems will have some sort of key column that can connect to reports. For example, one ERP system can have an internal invoice number, an invoice number to connect to another system and the other ERP can also have same connecting invoice number, and a different number that is sent to client. Here, merging two queries that are a connection can be used to put together into one table that shows the two internal ERP invoice number and the invoice number sent to client.  One ERP   Second ERP   Merge

Sample Employee Data Linkedin Learning Power Query: Get & Transform Drill Down Article I took this sample employee data that contains a 1000 rows of data and drilled down to variables, job title & department. I created 2 connections from a 2 tables that contain data validation. I learned this technique through the Linkedin Learning video, which is great for convenience and easy identification of honing on certain details instead of having to filter and unfilter manually.  Shout outs to Alt + A + V shortcut for data validation. Please note while you can adjust the job title & department, you cannot refresh the query on this blog or by opening on a workbook on another window. However, this does show the steps and idea behind taking extremely large data sets to hone in on x,y,z variables.

EXCELISFUN Tutorial RECORD.FIELD Please refer to the video by EXCELISFUN. 1) Use two xlookups where 1st one is returning the row and the second gives you the column array 2) Use Power Query Have two query connections for 2 tables For the lookup table, add a custom column that outputs the table with amounts In the custom column, first have it find the vendor code row Then outside of that, use Record.Field to refer to that row field and return the city amount

SPLIT COLUMN BY NON DIGIT TO DIGIT If you have a string of characters that are a mix of characters and number digits, you can use this. There is also split by digit to non digit.

Power Query M Below is an example of making a custom column. Instead of filtering by media type or vendor name, I can add a single custom column that will calculate the following All internet & Adswerve vendor paid 0 commission All internet media type paid 15% commission All other media types paid 10%

TRIM&CLEAN SPLIT COLUMN If I received a payroll report from a client during my role as a Project Analyst at Incentax, I would use power query to clean up the report by capitalizing first letter of each word, removing trailing spaces, and splitting the columns to separate first and last name. Thus, it is now in a clean and usable format to copy into a templated file.

Power Query Data Types Below, many companies use a chart of account to code different types of transactions. I used power query to convert those coded transaction to text and filter to find the ones that are account payable transactions.

Add a Column through Power Query It is helpful to add columns that are arithmetically dependent on a column that is already existing on the table. Below, I created one additional column that is 15% multiplied that of unbilled net and another additional column that is the sum of unbilled net and commission to get the gross.

 Website:  https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a Still learning about power query at the moment. Below is  1) How to import a csv file, using power query to remove all blank rows, and can refresh the query if you edit the csv.  2) Converting to excel data to table then using power query  3) Show common cleaning tasks (move column, remove column, split columns, conditonal column) 4) Blue tabs shows example of split column (similar to text to columns) 5) Orange tabs shows example of conditional column (Nice way to add columns based on certain if, then statements)