Exercise - Import water data

Completed

This exercise goes through the steps that Reed takes to ingest the spreadsheets that Alex provided. Data import is a vital task for bringing large volumes of data into Microsoft Sustainability Manager. This exercise uses Excel; however, many prebuilt connectors are available, and partners can build custom connectors to integrate with more data sources.

Important

To download the sample Excel files to ingest into Microsoft Sustainability Manager, go to waste sample data. In the GitHub page that appears, select the download button. Extract the two Excel files to a folder on your local computer for use in this exercise.

Sign in to Microsoft Sustainability Manager

  1. Use an In-Private or Incognito window and open the go to Sustainability Environment you created in Set up the Microsoft Cloud for Sustainability trial exercise.

  2. Sign in with the credentials of Reed Flores.

Import water quality data

Follow these steps to import water quality data:

  1. In the left navigation pane, select Data > Imports.

  2. Select New.

  3. Select POWER QUERY CONNECTORS.

    Screenshot of the options with Power Query Guided Experience highlighted.

  4. Select Sources > Water. Select Add next to Water samples.

    Screenshot of the Define your data screen with Sources set to Water and with the Category name showing water quantity and quality data with the Add button for Water samples highlighted.

  5. Select Next after you add the data.

  6. Select Excel workbook.

  7. Select Upload file. Browse to the Water Sample Data Wide World Importers 2022.xlsx file saved on your system. Select Open.

  8. Select Sign in to sign in with your admin credentials.

  9. After you upload the file and the connection is complete, select Next.

  10. Select the Water Sample Data sheet and then select Transform data.

    Screenshot of the Water Sample Data sheet loaded and the Transform data button highlighted.

  11. You can complete various data and column transformations on the Transform data page of the Power Query wizard. As a result, you can adjust data types, update column mappings, and perform advanced transformations that you're familiar with in Microsoft Power Platform dataflows or Microsoft Power BI datasets. Because you don't apply transformations in this exercise, select Create.

    Screenshot of the Manage data source screen showing Queries and Query settings with the Create button highlighted.

  12. Go to the New data connection wizard on the Schedule data import page, where you complete the following actions:

    1. Turn on the Import data automatically toggle to allow the option for you to set a schedule for the data to be imported automatically. Selecting this option is beneficial if you use the connector in a scenario where the data changes frequently, such as a web API or FTP server.

    2. Turn on the Replace previously imported data toggle to remove all previously imported data and bring in the full dataset that was retrieved. Selecting this option is beneficial if the data source isn't providing data from only the last import or if it always includes a full set of data. For this scenario of importing historical data, leave both options turned off.

  13. Select Next.

    Screenshot of the Schedule data import area, showing the Next button.

  14. On the Review and name page, complete the following tasks:

    1. Enter a name for the new connection, such as Water Sample data.

    2. Select Connect.

    Screenshot of the Review and name area filled in and showing the Connect button.

  15. Map your source data to the data model. Data doesn't appear until this step is complete. Select Map fields.

    Screenshot of the Map fields button on the You've completed the initial data setup screen.

  16. Select Water samples in the Data source field. Select Auto map.

    Screenshot of the Mapping Water Sample Data screen with the Auto map button and the selected data source highlighted.

  17. Verify that the Destination and Source fields are mapped correctly. After reviewing your field mappings, switch the Ready to import toggle to yes. Select Save, select the back arrow, and then select Done.

    Screenshot of the Mapping Water Sample Data screen with the Ready to import option set to yes and with the mapped fields and the Save button highlighted.

  18. On the Data imports screen, view the import that you created.

    The Data import job runs, and the status displays as Scheduled. In a moment, the status switches to Processing. You might need to refresh your page to view the change.

  19. After a minute or two, select Refresh to view the updated status, which should be Complete.

    Screenshot of Data imports showing status complete.

  20. Select Data > Water data on the left navigation pane.

  21. Select Water samples under Water quality data.

    Screenshot of the Water data area with Water samples highlighted under Water quality data.

  22. Under Organization unit, filter by Wide World Importers. After a few moments, the view refreshes and the activity data records that were imported during this exercise are displayed.

    Screenshot of the all water samples view refreshed to show imported records.

Import water quantity data

This exercise goes through the steps that Reed takes to ingest the water quantity data for 2022 that Alex provided. This exercise uses Excel; however, many prebuilt connectors are available, and partners can build custom connectors to integrate with more data sources.

  1. Select Data > Data imports from the left navigation pane.

  2. Select New.

  3. Select POWER QUERY CONNECTORS.

  4. Select Add next to Water quantities.

    Screenshot of the Define your data screen with Water selected under Sources and Add selected next to Water quantities.

  5. After you select Add, select Next.

  6. Select Excel workbook.

    Screenshot of the Choose data source screen, showing Excel workbook under New sources.

  7. Select Upload file. Browse to the Water Quantity Data Wide World Importers 2022.xlsx file saved on your system. Select Open.

  8. Select Sign in, and you're signed in with your admin credentials.

  9. After the file is uploaded and the connection is complete, select Next.

  10. Select the Water Quantity Data sheet and then select Transform data.

    Screenshot of the Choose data area, showing the Water Quantity Data sheet loaded and the Transform data button highlighted.

  11. You can complete various data and column transformations on the Transform data page of the Power Query wizard. As a result, you can adjust data types, update column mappings, and perform advanced transformations that you're familiar with in Microsoft Power Platform dataflows or Microsoft Power BI datasets. Because you don't apply transformations in this exercise, select Create.

    Screenshot of the Manage data source screen, showing the Water Quantity Data query and the Create button.

  12. Go to the New data connection wizard on the Schedule data import page, where you complete the following actions:

    1. Turn on the Import data automatically toggle to allow the option for you to set a schedule for the data to be imported automatically. Selecting this option is beneficial if you use the connector in a scenario where the data changes frequently, such as a web API or FTP server.

    2. Turn on the Replace previously imported data toggle to remove all previously imported data and bring in the full dataset that was retrieved. Selecting this option is beneficial if the data source isn't providing data from only the last import or if it always includes a full set of data. For this scenario of importing historical data, leave both options turned off.

  13. Select Next.

    Screenshot of Schedule data import, showing the two toggle options and the Next button.

  14. On the Review and name page, complete the following tasks:

    1. Enter a name for the new connection, such as Water Quantity data.

    2. Select Connect.

    Screenshot of the Review and name page with the Name field filled in and the Connect button highlighted.

  15. Map your source data to the data model. Data doesn't appear until this step is complete. Select Map fields.

    Screenshot of the Map fields button on the You've completed the initial data setup screen again.

  16. Select the Data source to map. For this exercise, select Water quantities. Select Auto map.

    Screenshot of the Mapping Water Quantities Data page with Data source set to Water quantities and with the Auto map button highlighted.

  17. Verify that the Destination and Source fields are mapped correctly. Select Save.

  18. Select the Ready to import toggle and then select the back arrow.

  19. Select Done.

  20. On the Data imports page, view the import that you created.

    The Data import job runs, and the status displays as Scheduled. In a moment, the status switches to Processing. You might need to refresh your page to view the change.

  21. After a minute or two, select Refresh above the list to view the updated status, which should be Complete.

    Screenshot of the refreshed list showing a status of Complete.

  22. Select Data > Water data from the left navigation pane.

  23. Select Water quantities under Water quantity data.

    Screenshot of the water quantities option with Water quamtity data selected.

  24. Under Organization, filter by Wide World Importers.

After a few moments, the view refreshes and the activity data records that were imported during this exercise are displayed.

Screenshot of the All water quantities view refreshed to show imported records.