I just want to convey that the content of this site is completely copyright free. You can use the content the way you want without seeking any permission. Whether you are using it commercially or non-commercially, personally or within your organization, it doesn't matter. There is no need to give any acknowledgement either that 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)
SQL Quiz 3
Power Query – Date.IsInQuarterToDate, Date.IsInMonthToDate and Date.IsInWeekToDate functions – Tips & Tricks 16
Power Query's M language provides a rich collection of Date functions. One of the functions offered by Power Query's M language is Date.IsInYearToDate function. This function will give True or False depending on whether date is within Year to Date range. Hence, if today's date is 28-Nov-22, then dates from 1-Jan-22 to 28-Nov-22 will be…
Tips & Tricks 193 – Add a Target Line to a Bar Chart
Last time, we saw how to add target line in a column chart (Link below for last post). This time, we will learn how to add target line to a bar chart. The file used in this video – https://1drv.ms/x/s!Akd5y6ruJhvhvUDtymUM7djBUsCQ?e=HIDZiy
Power Query – Make Text.ExcelTrim Function – Tips & Tricks 15
TRIM function of Excel and Text.Trim functions of Power Query both remove leading and trailing spaces (ASCII Char code 32) from a text. But Excel's TRIM goes a step further. If there are mulitiple spaces between words, it will reduce those to a single space between words. Hence if I have a string " Microsoft…
Tips & Tricks 192 – Add a Target line in your Charts
Outshine others by creating Target line in your charts which would clearly show when you hit the target The file used in this video – https://1drv.ms/x/s!Akd5y6ruJhvhvT3-uVo6EaAd_vBF?e=4CLXk2
Tips & Tricks 191 – Be Super Smart at Work, use SMART LOOKUP
Smart Lookup is often an ignored feature of Excel though present through Excel 2016. Very powerful and can increase your productivity to next level if used properly. File used in this tutorial – https://1drv.ms/x/s!Akd5y6ruJhvhvTlGQ9t04W4Pj1LC?e=RU7tQ9