Power BI helps you to generate reports from data stored in LMS365. To retrieve data from LMS365, you'll need the Power BI Desktop and the API key for your tenant.
The example in this article shows how to generate a report on learners' attendance. The report will also show tables with learners' name, email address, and course information.
To merge all the relevant tables into a single report and download it, follow these steps:
1. In the Data view in Power BI Desktop, find the Attendances data field, as described in this article.
2. In the Navigator page, select the tables you want to add to your report and Load them. In this example, we've selected the Users and Courses tables. For detailed instructions on how to open the Navigator page, see this article.
3. In the left-side navigation, select the Report view icon, then select Transform data > Transform data.
4. In the Queries panel on the left-hand side, select the Query1 table (the table you just uploaded that contains the Attendances columns) and select Merge Queries > Merge Queries.
5. On the opened Merge page:
- In the Query1 section, select the CourseId table (the column named CourseId).
- From the drop-down list, select Courses.
- In the populated section below this drop-down list, select the Id column.
- In the displayed Privacy levels window, select the checkbox to ignore the privacy levels and then Save.
- On the Merge page, from the Join Kind drop-down list, select Full Outer (all rows from both).
6. Select OK.
7. In the added Courses column, select the Expand icon and choose the columns you want to include. In this example, we've selected only the Title.
8. Select OK.
The Course.Title column will be displayed.
9. To merge the Users table, in the Queries panel on the left-hand side, select the Query1 table (the table you uploaded that contains the Attendances columns) and select Merge Queries > Merge Queries.
10. On the Merge page:
- In the Query1 section, select the UserId table (the column named UserId).
- From the drop-down list, select Users.
- In the populated section below this drop-down list, select the Id column.
- From the Join Kind drop-down list, select Full Outer (all rows from both).
11. Select OK.
12. In the added Users column, select the Expand icon and choose the columns you want to include. In this example, we've selected Title and Email.
13. Select OK.
The Users.Title and Users.Email columns will be displayed.
14. Once the required tables have been added, select Сlose & Apply.
15. In the left-side navigation, select the Report view icon.
16. In the Visualizations section, select the Table icon.
17. In the Data section, expand Query1 and select the checkboxes of the data fields you want to add to your report. In this example, we've selected the Users.Title, Users.Email, Courses.Title, and Attendances.Percent data fields.
The order in which you select the data fields will determine the order in which they'll be displayed in the report.
18. To preview the report, expand the view by dragging the bottom right-hand corner of the report window.
19. To export the report to your local device, select the More options icon (the horizontal ellipsis) > Export data.
20. Enter a filename, select the relevant file location, then Save. The file is saved in .csv format, which you can open in Excel. When opened in Excel, it'll look similar to the following image.
For more information about formatting your downloaded report in Excel, see this Microsoft documentation.
Comments
Article is closed for comments.