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

Dynamic Table If I add new data to my Billing Table, my sumif formula will not need to be updated to capture the full data. Within my sumif formula, I can refer to my Billing Table and then use bracketed columns and this will allow the sumif formula to capture all the data even if new data is added to the dynamic table.

ICON SETS Below is an example of green if we billed a job a 100%, yellow if billed 50% but below 100%, and red if 0% using icon sets.

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%

EXCELIFSUN Credits goes to the YouTuber EXCELSIFUN. I was familiar with idea of using MID and XLOOKUP.  However, I liked how he pointed out by adding a zero, it can convert the text after using the MID function to a number so that we can use the xlookup. Also, if you highlight all the cells, then enter the formula in the first cell of the highlighted array, then Ctrl + Enter, you can autofill the formula throughout that whole array.

EDATE EOMONTH I used EDATE to find the nth month later of the date, 07/14/2024. I also used Emonth to find the end of the month with respect to that date. If I go one month back, which will return the end of June (06) month then add 1, this will result with the first day of month.

FILTER An xlookup will produce a single result based on a criteria. A Filter can do the same but produce multiple results. Below I produce all results for vendor code "coosto" Then, I used the sort function to order it from smallest to largest.