Here are 7 Excel & Power Query challenges posted on Linkedin during Week 1. You can create your own solution and also can refer to solutions posted by others to learn from others. EXCEL CHALLENGE 1 2. EXCEL CHALLENGE 2 3. EXCEL CHALLENGE 3 4. POWER QUERY CHALLENGE 1 5. EXCEL CHALLENGE 4 6. EXCEL…
Category: Challenges
Important – Content of this Website is 100% Copyright Free
I just want to convey that the content of this site is completely copyright free. You can use the content the way you want without seeking any permission. Whether you are using it commercially or non-commercially, personally or within your organization, it doesn't matter. There is no need to give any acknowledgement either that you…
Solution – Running Total – Power Query Challenge 8
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
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…
Solution – Challenge 73 – Sum the Numbers in a String
Below are the possible solutions to the Challenge 73 – Sum the Numbers in a String Formula using traditional Excel functions =SUM(–(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*LEN(A2)+1,LEN(A2)))) Using FILTERXML (for office 2013 and greater and 365) =SUM(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s")) If you are not on Office 365, then you will need to enter above formula as array i.e. by pressing CTRL+SHIFT+Enter….
Challenge 73 – Sum the Numbers in a String
Let's say cell A2 has following string. 12,2, 2023,309,4, 557,62 Hence, there are following distinct numbers 12 2 2023 309 4 557 62 The challenge is to find the sum of these numbers. Hence answer should be 2969 which is 12+2+2023+309+4+557+62. The solution to this problem will be published on 28-Feb-22.
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…
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.