This is another interesting Power Query Challenge. Extract the digits from the given string and sum the digits for that extract string in each row.
Category: Challenges
Power Query Challenge 3 – Replacement of Words through Lookup Table
There is a table which has Original Text and there is another table which contains the list of words which need to be replaced. Original text should be replaced with words from lookup table.
Power Query Challenge 2 – Count the Frequency of Words
Here comes the challenge. The Subjects table has list of subjects. The words are separated by either comma or space and amount of space is also not uniform. The case of subjects is also not uniform. The count of rows for subjects is needed
Power Query Challenge 1 – Count Number of Words through UI
Here comes the Power Query challenge. The count of words in each row is needed. The challenge is that the solution has to be completely UI (user interface) driven. After loading the table in PQ, you should not press even a single key to come up with the result. You should use only mouse.
Solution – Challenge 72 – Unique characters string from a given string
A possible solution for the challenge – Challenge 72 – Unique characters string from a given string =CONCAT(UNIQUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))
Challenge 72 – Unique characters string from a given string
This time challenge is to generate unique characters string through a formula. Suppose, you have following following string in cell A2 Letters Now the output should be after extracting characters occurring once only. Letrs Some sample data to be be used Source String Result String adequate adequt stagnant stagn phenomenon phenom effective efctiv benevolent benvolt…
Solution – Challenge 71 – Determine the Rank
Below is a proposed solution to the Challenge 71 – Determine the Rank In E2, use following formula and drag down =SUMPRODUCT(–($B$2:$B$6+$C$2:$C$6+$D$2:$D$6>SUM(B2:D2))) +SUMPRODUCT(–($B$2:$B2+$C$2:$C2+$D$2:$D2=SUM(B2:D2)))
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…
Solution – Challenge 70 – Need a Data Validation Formula
Below is a proposed solution to the Challenge 68 – Need a Data Validation Formula =IFERROR(IF(ISNUMBER(SEARCH("/",A2)),IF(–RIGHT(A2,4)=2018,AND(–LEFT(A2,2)>=1, –LEFT(A2,2)<=12),IF(AND(–RIGHT(A2,4)<=2021, –RIGHT(A2,4)>=2019),AND(–LEFT(A2,2)>=1,–LEFT(A2,2)<=4)))),FALSE)
Challenge 70 – Need a Data Validation Formula
Need a data validation formula for following assuming you are putting data validation in cell A2. The user should be able to enter a date in following format MM/YYYY for the year 2018 QQ/YYYY for the years 2019 to 2021 The solution to above challenge will be published after a month i.e. on 16-Jan-18.