Power Query – Change Index at each discontinuity. Sometimes, you need to insert index but change is needed at when value in left column changes
Challenge 71 – Determine the Rank
Column A has players and there are 3 rounds of a game. Columns B to D contain the points achieved by the players. The challenge is to determine the rank of these players on the basis of sum of points in various rounds without a helper column. 1 would mean the highest rank. Column E…
Excel Quiz 52
Inserting a Sequence 1,2,3,1,2,3,1,2,3…..- Power Query – Tips & Tricks 3 –
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,2,3,4,1,2,3,4,1,2,3,4
Tips & Tricks 178 – Extract last 2 words OR last n words
To extract last 2 words, use below formula =IF(ISNUMBER(FIND(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2))),"") To make a generic formula, to extract last 2 words =IF(COUNTIF(A2,"* *")>0,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2))),"") Now, you would need to change * * and 2 only which are marked in Red. If you need to replace last 3 words, then * * would be…
Downloads 22 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – IBM HR Analytics for Attrition
On Kaggle there is a data set published named "IBM HR Analytics Employee Attrition & Performance" to predict attrition of your valuable employees. This is a very popular dataset and has usability index of 8.8. https://www.kaggle.com/pavansubhasht/ibm-hr-analytics-attrition-dataset But this data set has only 1470 rows whereas we need, sometimes, a large data set for testing. So,…
Inserting a Sequence 1,1,1,2,2,2,3,3,3,4,4,4….- Power Query – Tips & Tricks 2
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,1,1,1,2,2,2,2,3,3,3,3 and so on.
Tips & Tricks 177 – Extract first 2 words OR first n words
You can use following formula to extract first 2 words from a text string =IFERROR(LEFT(A2, FIND(" ", A2&" ", FIND(" ", A2) + 1) – 1),"") A generic formula to extract first 2 words which can be extended to n words =IFERROR(LEFT(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(A2&" "," ",REPT(" ",LEN(A2)),2))-1),"") =IFERROR(REPLACE(A2,FIND(REPT(" ",LEN(A2)),SUBSTITUTE(A2&" "," ",REPT(" ",LEN(A2)),2)),LEN(A2),""),"") To extract first n…
Excel Quiz 51
Count Number of Words – Power Query – Tips & Tricks 1
Many a time, you are required to count number of Words in a record. The classic formula in Excel happens to be =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1 Note – If you have blank cells also, then right formula is =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+(LEN(A2)<>0) To calculate, you can always mimic the above in a custom column in PQ by following =…