Challenge No. 8: (Excel formulas also welcome)
Clean the data and generate the result table as shown.
Solved using: Power Query (Table.AddColumn, Table.TransformColumns).
⭐️⭐️⭐️⭐️ Advanced
Complex challenges for experienced users.
Extract From Text! Part 2
Challenge No. 78: Extract all the Dates from the text provided in the question table in different formats.
Solved using:Excel, Power Query, Python, R, and VBA.
Missing Values! Part 2
Challenge No. 62: The question table presents the actual progress of 3 projects at the end of each month, but some values are missing.
Solved using:Excel (DATE, DROP, IF), Power Query (Date.Month), Python, and R.
Match Payments!
Challenge No. 60: The customer receipt costs and their payments are provided in Question Tables 1 and 2.
Solved using:Excel, Power Query, and R.
Warehouse Management!
Challenge No. 55: In the question table, transactions for different products are presented.
Solved using:Excel (FILTER, HSTACK, INDEX), Power Query (List.Distinct, Table.Group, Table.Pivot), Python, and R.
Table Transformation! Part 7
Challenge No. 48: The table below presents various combinations of heuristic algorithms used to solve a problem, each combination separated by a “+”.
Solved using:Excel (COUNTIF, HSTACK, IF), Google Sheets, Power Query (List.Distinct, List.Transform, Table.AddColumn), Python, and R.
Custom Grouping! Part 2
Challenge No. 46: The “Question” table provides a list of Product IDs.
Solved using:Excel (CHOOSECOLS, DROP, FILTER), Power Query (Table.Group, List.Transform, Text.End), Python, and R.
Cross Selling!
Challenge No. 40: In online markets, when customers add items to their carts, other products, known as complementary products, which are often purchased with the selected items, are suggested to boost sales.
Solved using:Excel (ARRAYTOTEXT, FILTER, HSTACK), Power Query (List.Transform, Text.Split, Table.AddColumn), and R.
Connected People!
Challenge No. 37: In the question table, the IDs of 20 people who call each other are provided.
Solved using:Excel (FILTER, HSTACK, LAMBDA), Power Query (List.Transform, Text.Combine), Python, and R.
Remove Outlier Questionnaires!
Challenge No. 33: Questionnaires are a common method for collecting data, but they are susceptible to noise from respondents who fill them out randomly.
Solved using:Excel, Power Query, Python, and R.