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…
Category: Tips and Tricks
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
Tips & Tricks 190 – Blazing fast way to convert text numbers into numeric numbers
No need to write formulas to convert text numbers into numeric numbers. Here is a blazing fast way. File used in this tutorial – https://1drv.ms/x/s!Akd5y6ruJhvhvTY9-xarJTpl0fKs?e=jeMABJ
Tips & Tricks 189 – Extract First and Last Name in a Super Fast Way – No Formula Required
No need to write formulas to extract first name and last name. Here is a super fast way by using CTRL+H. The same trick can be used to extract user name and domain name from a mail ID. File used in this tutorial – https://1drv.ms/x/s!Akd5y6ruJhvhvTPIrUd2QVh1ro7x?e=SyaAMr
Tips & Tricks 188 – Running Total and Percentage Total without Formulas
Are you still writing formulas to calculate Running Total and % Total? There is a super fast way to calculate Running Total and % Total. Use this to work at a blazing speed in Excel while calculating Running Total and %Total. No more formula writing. File used in this tutorial – https://1drv.ms/x/s!Akd5y6ruJhvhvTGzqZqO-VkYFWID?e=IUzfQx
Power Query – Generate Fiscal Year – Tips & Tricks 14
Suppose you need to generate fiscal year like 2021/22, then use this formula in a custom column where 3 means FY finishes in March month. Similarly say your financial year finishes in Jun month, replace 3 with 6..If it finishes in Jul, replace 3 with 7. You will need to replace [Year] and [Month] fields…
Power Query – Sum only Numbers in a List – Tips & Tricks 13
Suppose you are given a list {"x", "100","200", "x300", 500} and you need to sum only the numbers 100, 200 and 500 in it. Then you can use following code