This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
Announcement | We'll be doing maintenance between 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!
So firstly I used data cleanse to just remove the rows with null entries. Next a RegEx match and a select tool was used to match the rows in field 1 that began with a number. A running total was used so that all the information denoted to stadium 1 was marked with a 1, a 2 for stadium 2 and so on. Text to columns was used to parse the data so that we had a category in one column and the data in another. A regex replace was used to turn the categories that were just numbers into 'Location'. A cross tab was used so that these categories could be the column headers and they would be grouped by the running total that was calculated earlier.
A select tool was then used to remove all the irrelevant data. Next I used text to columns to seperate the data in the location column and then use a select tool to rename the two new columns Stadium and City and remove the irrelevant column. Again text to columns was used, this time to split up all the different games that were taking place in each stadium into seperate columns. A transpose tool was used to then place all these 'game' columns into one column that listed the game number and the actual fixture along with the date in another column. Again a select tool was used to remove the column that stored the game numbers. Another text to columns used to parse out the date from the fixtures column. The filter tool was used to remove some of the null entries in the 'Value' columns, these occurred when the particular stadium was being used for less than the maximum number of games (7).
Another Select tool was used to rename columns and exclude non-relevant ones. While parsing the date from the fixtures the end bracket was still attached to the date so a datacleanse was used to remove that before a formula using DateTimeParse was used to convert the date into the required format. Finally select and sort tools were used to reshuffle the columns into the correct order given by the output and sorted by date to put the fixtures in the correct order.