SumTotal
If you’re looking to connect to a SumTotal data source from inside Power BI, then you’re in the right place. In this article, we’re going to show you how to use Power Query M Language Code to establish a connection.
What is Power Query M Language Code?
Power Query M Language Code is a programming language that is used to query and transform data. It is built into Power BI and is used to retrieve data from a wide range of sources, including databases, web services, and files.
Connecting to a SumTotal Data Source
To connect to a SumTotal data source from inside Power BI, you will need to follow these steps:
1. Open Power BI and select the “Get Data” option from the Home tab.
2. In the “Get Data” window, select the “Blank Query” option from the “Other” section.
3. In the “Query Editor” window, select the “Advanced Editor” option from the “View” tab.
4. In the “Advanced Editor” window, enter the following Power Query M Language Code:
“`let
Source = OData.Feed(“https://yourSumTotalurl.com/SkillsoftData/odata/v4/”),
SkillsoftData_Skill = Source{[Name=”SkillsoftData.Skill”]}[Data],
#”Expanded SkillsoftData_Skill” = Table.ExpandRecordColumn(SkillsoftData_Skill, “Skill”, {“Name”, “ID”, “Description”, “Duration”, “Keywords”, “ThumbnailURL”, “URL”}, {“Skill.Name”, “Skill.ID”, “Skill.Description”, “Skill.Duration”, “Skill.Keywords”, “Skill.ThumbnailURL”, “Skill.URL”})
in
#”Expanded SkillsoftData_Skill”“`
5. Replace the “https://yourSumTotalurl.com/SkillsoftData/odata/v4/” with the URL of your SumTotal data source.
6. Click the “Done” button to close the “Advanced Editor” window.
7. Click the “Close & Apply” button to save the changes and close the “Query Editor” window.
Explaining the Power Query M Language Code
Now that you know how to connect to a SumTotal data source from inside Power BI using Power Query M Language Code, let’s break down the code and explain what each part does.
Line 1: let
The “let” statement is used to define a query that can be reused throughout your Power BI report. It is followed by a series of statements that define the query.
Line 2: Source
The “Source” statement defines the source of the data. In this case, we are using the OData.Feed function to retrieve data from a SumTotal data source.
Line 3: SkillsoftData_Skill
The “SkillsoftData_Skill” statement defines the table that we want to retrieve data from. In this case, we are using the table named “SkillsoftData.Skill”.
Line 4: Expanded SkillsoftData_Skill
The “Expanded SkillsoftData_Skill” statement expands the table to include all of the columns that we want to retrieve. In this case, we are expanding the “Skill” column to include the “Name”, “ID”, “Description”, “Duration”, “Keywords”, “ThumbnailURL”, and “URL” columns.
Conclusion
In conclusion, connecting to a SumTotal data source from inside Power BI using Power Query M Language Code is a straightforward process. By following the steps outlined in this article, you can easily establish a connection and start retrieving data from your SumTotal data source.