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…
Author: Vijay A. Verma
Tips & Tricks 183 – Using FILTERXML to extract nth word from front and back
Suppose you have following string Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin And you want to retrieve nth word from front. You can use following FILTERXML formula for this =FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[N]") Where N need to be replaced with the word number which you want. Hence, if you wanted 2nd word, then replace that with…
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…
Power Query Challenge 6 – Generate Multiple Columns
This time you would need to generate many columns from a single column sorted through Power Query. The problem has many alphabets in single column and you will need to generate columns of alphabets which are sorted.
Power Query Challenge 5 – Generate Multiplication Table
This is 5th in series of Power Query Challenges. Given the height and width, you need to generate the Multiplication Table. In the example. Height is 20 and width is 8, hence multiplication table columns should be from 1 to 8 and each should contain 20 rows.
Power Query Challenge 4 – Extract digits and sum them
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.
Power Query Article 1 – NetworkDays using List.Accumulate
This article on Power Query contains one line m-code for calculating NETWORKDAYS.INTL using List.Accumulate. Hence, you can control weekends as well as list of holidays.
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.