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))
Author: Vijay A. Verma
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…
Running Total – Power Query Challenge 8
Calculate the running total for each row and populate with running total for each row separated by comma. Of course, the query should be dynamic i.e. even if number of entries increase or decrease in a line OR/AND number of rows increase or decrease, the Query should give right result on Refresh. Try to do…
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…
Solution – Challenge 73 – Sum the Numbers in a String
Below are the possible solutions to the Challenge 73 – Sum the Numbers in a String Formula using traditional Excel functions =SUM(–(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*LEN(A2)+1,LEN(A2)))) Using FILTERXML (for office 2013 and greater and 365) =SUM(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s")) If you are not on Office 365, then you will need to enter above formula as array i.e. by pressing CTRL+SHIFT+Enter….
Excel Quiz 54
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…
Challenge 73 – Sum the Numbers in a String
Let's say cell A2 has following string. 12,2, 2023,309,4, 557,62 Hence, there are following distinct numbers 12 2 2023 309 4 557 62 The challenge is to find the sum of these numbers. Hence answer should be 2969 which is 12+2+2023+309+4+557+62. The solution to this problem will be published on 28-Feb-22.
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(",…