Challenge No. 166: In the provided table, the Date Time values correspond to different time zones (specified in the column GMT From).
Solved using:Excel (BYROW, HSTACK, LAMBDA), Google Sheets, Power Query (Text.End), Python, Python in Excel, and R.
Python for Data Analysis
Using Python for data analysis
Custom Grouping! Part 13
Challenge No. 165: Based on monthly transactions, categorize each customer for each month into one of the following groups:
New: A customer who has never made a purchase in previous months.
Solved using:Excel (FILTER, HSTACK, IF), Google Sheets, Power Query (Table.Group, List.Difference, List.Distinct), Python, and R.
Extract From Text! Part 6
Challenge No. 164: In Power Query, a list is defined by { } and can contain sublists, such as {1, 2, {3, 4}}.
Solved using:Excel, Google Sheets, Power Query, Python, and R.
Custom Grouping! Part 12
Challenge No. 163: Convert the monthly sales data from the Question table into seasonal sales as shown in the Result table.
Solved using:Excel (FILTER, GROUPBY, HSTACK), Google Sheets, Power Query (Table.Group, Table.AddColumn, Table.Group), Python, Python in Excel, and R.
Extract From Text! Part 5
Challenge No. 162: Separate the values inside the curly braces { }, do the same for nested curly braces.
Solved using:Excel (IFNA, LAMBDA, LET), Google Sheets, Power Query (List.Transform, Table.AddColumn), Python, Python in Excel, R, and VBA.
Custom Grouping! Part 11
Challenge No. 161: Extract the list of dates associated with each product and display them under each other in separate columns.
Solved using:Excel (DROP, FILTER, HSTACK), Google Sheets, Power Query (Table.Group), Python, Python in Excel, and R.
Eight Knights Puzzle!
Challenge No. 160: The problem aims to place 8 knights (hours) on a chess board in such a way that each row/column is occupied by a single knight and no knight removes the other one from the chess board.
Solved using:Excel, Power Query, Python, Python in Excel, and R.
Filter Last Transaction!
Challenge No. 158: From the question table, extract the last transaction per product for each month.
Solved using:Excel (DROP, FILTER, GROUPBY), Google Sheets, Power Query (Date.Month, Table.AddColumn, Table.Combine), Python, Python in Excel, and R.
Table Transformation! Part 18
Challenge No. 157: Transform the question structure into the result structure.
Solved using:Excel (HSTACK, INDEX, LAMBDA), Google Sheets, Power Query (List.Split, List.Transform, Text.Split), Python, Python in Excel, and R.
Column Splitting! Part 3
Challenge No. 156: If the IDs contain an even number of characters, split them into two columns at the midpoint.
Solved using:Excel (HSTACK, IF, IFNA), Google Sheets, Power Query (List.Transform, Text.End, Text.Start), Python, Python in Excel, and R.