Nsw Exploration And Mining Applications Project Assessment

1. To complete this assessment, you must complete the following tasks to industry standards:
   • Set-up Project Folders and Check Excel Software
   • Develop a linked spreadsheet solution
   • Implement automated spreadsheet updates
   • Test workbook updates correctly
   • Create and format output to meet task requirements
   • Finalise documentation and inform appropriate person
2. Before you start, read through the entire assessment.
3. Refer to the Resource Document for organisational guidelines for tasks.
4. Use the information below and the criteria listed on the Assessment checklist to guide your responses.
5. Once completed, zip all files together, upload to Moodle/RI Learn a submit this assessment to your Teacher/Assessor for marking.

Project Brief

1. You have been supplied with two sets of data, a spatial dataset, and an Excel spreadsheet. The spatial dataset is a polygon layer of current New South Wales exploration and mining tenement applications. The Excel spreadsheet contains a list of current applications which indicates if all the required forms have been submitted with each application.
2. Both datasets are updated monthly, and you are required, monthly, to merge these datasets to create a list of applications that do not have all the submitted forms. Your list will be used by a tenement application officer to contact applicants to request the missing documentation.
3. The output worksheet needs to include four (4) columns: Application, Applicant, Appln_date and All_Forms_Supplied. The worksheet needs to be sorted in ascending order on Application.
4. You are required to use Excel to set-up an automated process to merge the two datasets and create the output using new sets of data that are supplied each month.

Documents you will need for this task are:

• Assessment event (this document)
• Supplied files in the table below (Pro_Part2_Files)
• CPPSSI5060_AE_Pro_ResourceDocument.pdf

You have been supplied with two sets of files: one set to create your Excel workbook and a second set to test that the automation process works.

Supplied files:

This is the link to metadata for NSW Current Exploration and Mining Applications.

Task 1: Set-up Project Folders and Check Excel Software

1.1 Set up your project folders are per the organisational guidelines in the  supplied Resource Document. (CPPSSI5060_AE_Pro_ResourceDocument.pdf)

• It is important to save all your files in your Project file directory so your teacher/assessor can mark your assessment.
• Save the supplied files (Pro_Part2_Files) in your Project > Data > Source Data directory
• Include a screen shot below of your project folder layout with your assessment zip file.

1.2 Check your version of Excel for functionality. You will need to use Get & Transform in Excel, previously known as Power Query (Excel 2013 and earlier).

• Use this Microsoft support page on Power Query data sources in Excel versions and fill out every row in the Get & Transform Functionality column to complete the following table.
• Tick the version you are using to make sure you have the required Excel functionality to complete the assessment task.

Task 2: Import SHP file and Plan Spreadsheet Design Parameters

2.1 Using the supplied SHP file (NSW_ TitleAppln.shp) import into your GIS software (QGIS or ArcGIS).

2.2 Review the fields and the data in the spatial layer and the Applns_AllForms.xlsx file to plan the required fields and data types to design the output worksheet of your Excel workbook.

• Fill out the table below to design your spreadsheet design parameters for the supplied spatial layer by viewing the dataset table in your GIS software and referring to the organisational guidelines in the Resource document.

Hint: Use the following links to review how to view the spatial data as a table in GIS software:

Open an attribute table In QGIS

View attribute information in ArcGIS

Recall from Task 1: The output worksheet needs to include four (4) columns: Application, Applicant, Appln_date and All_Forms_Supplied. The worksheet needs to be sorted in ascending order on Applicant and only show applications where the applicant has not supplied all the required forms.

• The first row has been completed for you, as a guide.
• Output Worksheet Design Parameters

Task 3: Set up Data Source files

3.1 In your GIS software, export the spatial layer to CSV format so you can import the dataset into Excel.

Name this file, NSW_TitleAppln.csv and save the file in your MyProject\Data\WorkingData file directory so it is included in your assessment submission.

3.2 Move the Applns_Allforms.xlsx to your MyProject\Data\WorkingData file directory so it is included in your assessment submission.

Task 4: Query 1 - Import and edit CSV file

Review this resource, Using Power Query Editor, for help completing Tasks 4 to 8.

Import your NSW_TitleAppln.csv file into Power Query Editor.

4.1 Use Home > New Query > New Source > File – navigate to your MyProject\Data\WorkingData directory and import the NSW_TitleAppln.csv file.

4.2 Under Query Settings you can change the name of your Query if you want to! (by default it will be the file name)

4.3 In the Transform tab, make any required changes to the columns and data types (for example change appl_date format)

4.4 Rename query steps to something that describes the step (for example ‘Update date format’)

Task 5: Query 2 - Import and edit Appln_Forms.xlsx

Import the second file ImportAppln_Forms.xlsx into Excel as a query in Power Query Editor.

5.1 Use New Query > New Source to import the Applns_AllForms.xlsx file.

5.2 Under Query Settings you can change the name of your Query if you want to! (By default it will be the file name)

5.3 In the Transform tab, make all the required changes to the columns and data types.

Hint: You will need to combine the Appln_Code and Appln_no columns so you have a matching column that you can use to join the two queries in Task6.

Try Transform > Merge Columns

Task 6: Query 3 – Link spatial data

Join the two queries together to make a third query – this will become your final output worksheet.

Select Home > Merge Queries as New.

• Select the two queries and set Join Kind to Inner.
• Select the matching (linking) column in the two queries.
• Select OK
• Expand the merged columns of Appln_FormsCheck in the query view (so you can see all columns from both queries).
• In the Query Settings Panel, rename the Query for this step.

Task 7: Format Output

• Finalise the merged query output to meet the output task requirements as planned in Task 2.2.
• Recall from the Project Brief: The output worksheet needs to include four (4) columns: Application, Applicant, Appln_date and All_Forms_Supplied. The worksheet needs to be sorted in ascending order on Applicant and only show applications where the applicant has not supplied all the required forms.
• Rename each step in your query to something that describes what you did in that step.        

Task 8: Load & Finalise Excel Workbook

Once you have finalised all your query steps you can export the queries to a workbook.

8.1. Select Home > Save Close and Load to…Save the workbook to the Project > FinalProduct > Documents set up in Task 1.

8.2. Make any final edits to style and formatting as per the organisational guidelines in the Resource document.

8.3. Save a copy of the output worksheet to MyProject > FinalProduct >Documents by printing to PDF (Select A4, Landscape, Fit Sheet on one Page)

Task 9: Update the Workbook output with new data

The tenement application officer has a new Applns_AllForms.xlsx file, so you need to update the Workbook output by updating this file and choosing Select Data > Refresh All

Any new source data files need to be of the same name and to be in the same original directory so the Power Query Refresh Data will work.

These steps will guide you through the process:

9.1  Archive the original supplied data Applns_AllForms.xlsx to the correct folder as per the organisational guidelines (Backup Folder).

9.2  Copy the new Applns_AllForms.xlsx file into the directory where the original data was stored (this file is included in the supplied assessment data in SecondDataset folder)

       For this assessment we will re-use the CSV file of NSW tenement applications, so there is no need to archive or move this file.

9.3.  To automatically update output worksheet – use Select Data > Refresh All

9.4.  Save a copy of the output worksheet to MyProject\FinalProduct\Document by printing to PDF (Select A4, Landscape, Fit Sheet on one Page)

Task 10: Test worksheet

• Check that your worksheet output has refreshed correctly.
• Briefly describe (2-3 dot points) what you did to make sure the worksheet has refreshed correctly, and the Power Query – Refresh All has worked!
• Click or tap here to enter text.              

Task 11: Protect Worksheet

Protect your output worksheet from accidental Data Refresh updates. Record the password in the Documentation worksheet (Task 12) so your teacher/assessor can mark your assessment.

Task 12: Complete documentation

• Insert a worksheet in your workbook and enter workbook documentation.
• Guidance for this task is provided in the Resource Document.
• Using the space below, write an email to the Tenement Applications officer and let them know you have completed the task, where the workbook is stored and how to refresh if the source files have been updated and the password

Task 13: Export Power Query Editor Code

• View the code for each of your three (3) queries, copy and paste to text files of the same name.
• Store in the Project > FinalProject > Documents directory and include in the zip file of your submission.

Hint: In Excel select Data > Launch Power Query Editor. Select each Query and choose Advanced Editor. Copy and paste the code into a text file.