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.

Textsplit Xlookup Large Rows Rows absolute & relative reference Explained Filter I decided to create one embedded formula that will produce nth largest invoice amount and its correlated invoice number. I used the large function to find the largest number based off nth criteria. For the nth criteria, I used the Rows function to make it count the rows so that it will go 1,2,3, etc. Then, I used an xlookup to find the largest invoice amount to output both the largest invoice number and the amount separated by a space. Finally, I used the textsplit to separate it by the space delimiter. However, I realized this formula is restricted by the xlookup only being able to output the first invoice number that appears in a table if there are multiple invoice amounts that are the same. Therefore, I did a conditional format to find which invoice amounts are the same. Then, one can add that to the blue cell I2. The adjacent Filter formula will produce all the invoice numbers. Pretty happy about t...

SMALL & LARGE I used an if statement to produce the invoice amount if the vendor code matches the header of the table. This will create a spill of results because this can occur as little as 0 times or as many times. Now, if I use the LARGE() function on the outside of the if statement, I can restrict it and produce the Nth largest invoice amount. This can be done likewise if I use the SMALL() function. See below.

SORT  &  UNIQUE Usually, I am use to using a pivot table to remove duplicates or use the remove duplicate option.  An alternative way and more simple way is to use the super helpful function UNIQUE() function & add on the SORT() function to have it in alphabetical order.

Small & Large Rows & Columns This is an extension of this post:  Small & Large & Row & Column I decided to embed within the Sum and Xlookup formulas the SMALL and LARGE function. I used it to find the sum of the 2 largest and smallest invoice amounts. I used the Xlookup to find the 2 smallest and largest invoice numbers this time.

Small & Large Rows & Columns The SMALL and LARGE function can return the smallest or largest value at the kth value. So it is not restricted to finding the ABSOLUTE smallest or biggest number. It can return the 2nd or 3rd smallest or largest, etc. This is useful if I have a large payment request and the treasury wants me to find the 2 largest invoices to send a pdf copy proof before the treasury approves and sends out ACH that Friday of the week. The ROW and COLUMN formula can return the respective row and column of a cell. The ROWS and COLUMNS returns the total number of rows and columns in an array/table.

As a Finance & Accounting Specialist, we pull different reports such as an AP import file. Then we paste them into a raw data tab into a template file so that it is in a suitable format to import into our accounting software. So I decided to create my own import template about Fruits and Vegetables. The if statement is used to change if a cell in the raw tab is blank. I use the xlookup to return the Category and AP code. I include an iferror so that it leaves a blank cell if the food is not recognized from the Legend tab. I also changed the date format on the output tab.

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.

Vlookup Hlookup Below use the dropdown list to select a client and the cells below with the Vlookup & Hlookup formula will return billing and commission numbers. Note that the first tab is using Vlookup because we are looking for the Client vertically in a column and the second tab is using Hlookup because we are looking for the Client horizontally in a row.