Power Query Split a Column Dynamically is a very important tool for PQ practitioners. You can use this to split your column into variable number of columns. This removes limitation of splitting a column on the basis of first time split if result columns are more than first time split columns.
Category: Tips and Tricks
Tips & Tricks 180 – Determine the date given the weeknumber
Given a year and week number, below formula finds the Sunday as the date. =MAX(DATE(F2,1,1),DATE(F2,1,1)-WEEKDAY(DATE(F2,1,1),1)+1+(G2-1)*7) Where F2 has the year and G2 has the week number. For week 1, this would always give 1-Jan as the date.
Tips & Tricks 179 – Retrieve Sheet Name in Excel Online
We can easily retrieve sheet name by following – Tips & Tricks 34 – Get Sheet Name through Formula But the formula depends upon CELL function. Excel Online i.e. web version of Excel doesn't support CELL function. It doesn't support VBA as well. Hence, we need to find an alternative to extract sheet name through…
Test for a Number – Power Query – Tips & Tricks 5
Test for a Number is a very important topic in Power Query Suppose, you have a column of values and you need to test whether the value contained is a number or not.
Change Index at discontinuity – Power Query – Tips & Tricks 4
Power Query – Change Index at each discontinuity. Sometimes, you need to insert index but change is needed at when value in left column changes
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…
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…
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 =…