Written by: Matt Walker, Senior Learning Administration Manager
The following set of videos contains step-by-step instructions on how to download Brightspace Data Sets, unzipping and organizing the CSV files, loading the data into Excel using Power Query, and ultimately combining User Attributes data with Users data to filter, group, and sort your Users data in new ways.
You'll learn a strategy for merging Full and Differential Data Sets together to have up-to-date data without having to wait for the next weekly Full Data Set Update.
You'll also see how to pivot the User Attributes data so that each Attribute is separated into individual columns.
Downloading data from Brightspace Data Hub
Go to Data Hub and find the Data Sets you need for your report. For information on what's available in Data Hub, as well as how the data is interconnected, refer to About Data Hub.
Full Data Sets are typically updated weekly, so you may need to download some of the more recent Differential Data Sets and combine or merge them. Data Sets are downloaded as zipped .CSV files that need to be unzipped before you can begin processing them.
Note: If you are combining Full and Differential Data Sets, they have the same filename when unzipped. Rename the unzipped files accordingly so that no files end up overwriting others unintentionally.
Video: Download and unzip files
Loading the CSV data into Excel for reporting
If you simply add all of the CSV files as different tabs and sheets in your Excel workbook, depending on their size, you may find that you exceed the row limits in Excel or your device's memory. LOOKUPS and other formulas in Excel increase processing demand as you add them. Before long, you may find Excel needing several seconds of processing time after each value you add to the mix.
To properly manage large data files and combine them into a single CSV, the data can be loaded into Excel using Power Query. In Power Query, you can properly merge Full and Differential Data Sets using the Group By function, and then merge your table with others.
Video: Import data into Excel
Pivoting and merging Data Sets in Power Query
To create a Users list, including Attribute Values, you need to merge the Users Data Set with the Attribute Definitions and Attribute Values. You need to pivot the Attribute Values by the Value column because the Attributes are stored as a single column of values. The result is a table with a separate column for each Attribute Value.
Then the Users list can be merged with the Attributes to create a new Users list, which now allows you to filter the users by Attribute.
Video: Pivoting and merging Data Sets
Creating a pivot table report in Excel
When you're done performing data transformations, you can load the resulting data into Excel as a new worksheet. The data can be viewed as is (CSV) or you can create a Pivot Table (or even a chart) from the output.
With a filterable list of Users, you can bring in Grades tool data and create a Grades Report that can be filtered by User Attributes.
Video: Creating a report