I have been posting daily challenges on Linkedin since last 3 years. You can access more than 1000 Excel + Power Query Challenges here. The best part if learning by reading through the solutions posted by other leading experts. https://www.linkedin.com/in/excelbi/
Author: Vijay A. Verma
Tips & Tricks 205 – Generate random numbers without duplicates
Say, you want 10 random numbers between 1 to 100. Then you can use following formula =INDEX(SORTBY(SEQUENCE(100), RANDARRAY(100)), SEQUENCE(10)) If rather than 1 to 100, then you can tweak SEQUENCE(100) accordingly. Say you need between 101 to 500. Then replace SEQUENCE(100) with SEQUENCE(500-101+1, , 101) => Note here, 101 is start number and 500 is…
Tips & Tricks 204 – Return Columns in Different Order in XLOOKUP
Use following formula for this =XLOOKUP(M2,C:C, CHOOSE({2,1}, D:D, F:F)) =XLOOKUP(M2,C:C, CHOOSECOLS(D:F,3, 1)) Sample File – T&T_204
Tips & Tricks 203 – Return non-adjacent columns from XLOOKUP
You can use following formula to return non-adjacent columns through XLOOKUP =XLOOKUP(M2,C:C, FILTER(D:F, {1,0,1})) =XLOOKUP(M2,C:C, CHOOSECOLS(D:F, 1, 3)) Sample File – T&T_203
Tips & Tricks 196 – Cartesian Product
Suppose there are two ranges A2:A4 = A, B, C B2:B5 = 1, 2, 3, 4 Cartesian product should give the result A1, A2, A3, A4, B1, B2, B3, B4, C1, C2, C3, C4 In Excel the formula would be =DROP(REDUCE("",A2:A4, LAMBDA(a, b, VSTACK(a, b & B2:B5))),1) In Power Query, the formula would be =…
Tips & Tricks 195 – Reverse Lookup in a Range
Sometimes, you have a dataset where you will need to perform lookup starting from last cell to first cell rather than usual first cell to last cell. With XLOOKUP function, the task has become easier as it gives a parameter to control this. While default is from first cell to last cell but if you…
Power Query – Bypass 3000 cells limit in Enter Data – Tips & Tricks 19
Question – I am trying to "Enter Data" in Power BI that is a large table. I am doing this to avoid a connection and avoid refreshing data. I can copy the data from a csv file but when I paste into Enter Data, I get an error with 3000 cell limit. Answer – 1. Import…
Power Query – Multi Column Exact Match XLOOKUP/VLOOKUP – Tips & Tricks 18
Learn to perform Multi column exact match in Power Query like XLOOKUP, VLOOKUP in Excel. The pbix file used in this can be downloaded from – https://1drv.ms/u/s!Akd5y6ruJhvhvxUlfh5W15hhMqT-?e=vg6eTL
Power Query – Exact Match XLOOKUP/VLOOKUP – Tips & Tricks 17
I wrote a tip earlier on this – VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7 Here it the video. Learn to perform exact match in Power Query like XLOOKUP, VLOOKUP in Excel. The pbix file used in this can be downloaded from – https://1drv.ms/u/s!Akd5y6ruJhvhvwxUuHqOA1XAZ33u?e=8ZDAik
Tips & Tricks 194 – Highlight Max & Min in your Data Simplified
Create impression on your management. Highlight Maximum and Minimum in your data effortlessly. Excel file used in this video – https://1drv.ms/x/s!Akd5y6ruJhvhvUYGCzWbpu5BaP2J?e=D57teS Below are simpler formulas to do this Across Rows – B2=MAX($B2:$E2), B2=MIN($B2:$E2) Across Columns – B2=MAX(B$2:B$10), B2=MIN(B$2:B$10)