Excel's TRIM function has a unique way to handle trimming unlike other programming languages / applications. It not only trims the leading and trailing blanks (spaces) but also all blanks in between other than single blank between two words. Official Excel documentation for TRIM says – "Removes all spaces from text except for single spaces…
Category: Tips and Tricks
Binary to Decimal (BIN2DEC) using List.Accumulate – Power Query – Tips & Tricks 10
The tip is originally published @ http://excel-inside.pro/blog/2016/06/06/bin2dec-in-power-query-implementations-of-list-accumulate-part-1/ My formula is a slight modification of this. You can use following formula to convert from Binary Decimal where [Binary Number] is the column which holds the binary number. List.Accumulate(Text.ToList([Binary Number]), 0, (s,c)=> s*2 + Number.From(c))
Decimal to Binary (DEC2BIN) using List.Generate – Power Query – Tips & Tricks 9
Power Query doesn't give an equivalent function to DEC2BIN . Following formula can be used to convert a decimal number to binary Text.Combine(List.Reverse(List.Generate(()=>[x=[Decimal Number]], each [x]>0, each [x=Number.IntegerDivide([x],2)], each (Text.From(Number.Mod([x],2)))))) Where [Decimal Number] is the column which holds decimal number. Classic logic to convert decimal to binary is used. The logic is published at many…
Tips & Tricks 185 – Referring to a Row above in Structured Reference Table
You have a table below and want to refer to a cell above for Sales column (The business case is that you want to have difference in Sales for current month compared to previous month) Quick and easy way is to use OFFSET formula =OFFSET([@Sales],-1,) But in Row 2, it will bring the column heading…
Multi Column VLOOKUP Formula – Power Query – Tips & Tricks 8
In the previous post, we looked into VLOOKUP formula for exact match but where single column was involved – VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7 In this, I am presenting formula which will work for any number of columns, hence it will work for 1 column, 2 columns and so on….
VLOOKUP/XLOOKUP Formula – Power Query – Tips & Tricks 7
Generally, a merge is performed and then various succeeding operations are performed to arrive at Exact Match for VLOOKUP/XLOOKUP. But all these steps can be replaced with a single formula in Power Query which is faster than merge operation. Read on….. Let's recap VLOOKUP formula for Exact Match. =VLOOKUP(A2,Sheet2!A:C,3,0) =XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C) This will look for exact…
Tips & Tricks 184 – Using FILTERXML to reverse a string of Words
Another FILTERXML hack. Suppose you have following string of words in cell A2 Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin You want to reverse this string of words and want following output. Berlin, Detroit, Miami, Washington, Delhi, Paris, London, Moscow FILTERXML will come to your rescue. You can use following formula for this =TEXTJOIN(",…
Tips & Tricks 183 – Using FILTERXML to extract nth word from front and back
Suppose you have following string Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin And you want to retrieve nth word from front. You can use following FILTERXML formula for this =FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[N]") Where N need to be replaced with the word number which you want. Hence, if you wanted 2nd word, then replace that with…
Tips & Tricks 182 – Determine Quarter for Fiscal Year
Few countries follow different quarter other than Q1 from Jan-Mar and Q2 for Apr-Jun. In case of Jan-Mar as Q1, formula is simple (if cell A2 is date) =ROUNDUP(MONTH(A2)/3,0) This will give result as 1, 2, 3 & 4 for the quarters. If you want, you can concatenate "Q" in the formula to show Q1,…
Tips & Tricks 181 – Insert Sequence / Serial Numbers in a Pivot Table
Sometimes, it is important to show sequence numbers such as 1, 2, 3…..and so on. If you use database technologies, this is known as index column. 1, 2, 3…..are also used to denote rank. Suppose you have a pivot table which is sorted in descending order for the value. For the sake of showing ranking, you…