Below is a possible solution to the Running Total – Power Query Challenge 8. And code for Function The solution file can be downloaded from PQ_Challenge_8_Solution M
Category: Power BI
Convert Month Name to Month Number in Power Query – Tips & Tricks 12
In Query M language, there is no function which converts Month Name to Month Number so that if I give February or Feb, the result should be 2. You can use following formula to get the Month Number from Month Name = Date.Month(Date.FromText("1"&[Month Name]) If your data is using some other locale than whatever is…
Excel Trim Formula in Power Query – Tips & Tricks 11
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…
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…
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…
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…
Solutions to Power Query Challenges – 1 to 7
Attached Excel files contain solutions to all Power Query challenges posted till date (1 to 7). Please note that there are multiple solutions possible and the solution contained in these files is one of the approaches possible. Many people will come up with their own innovative solutions. PQ_Challenge_1_Solution PQ_Challenge_2_Solution PQ Challenge_3_Solution PQ_Challenge_4_Solution PQ_Challenge_5_Solution PQ_Challenge_6_Solution PQ_Challenge_7_Solution…
Clean the data – Power Query Challenge 7
You would need to clean the data to give the result in picture. Try to do maximum steps through UI and minimize formula / M-code. The problem file related to this challenge can be downloaded from PQ_Challenge_7_Problem The solution to this Clean the data – Power Query Challenge 7 will be published after a week…