SIS-CC SDMX
Power Query is a data connectivity tool in Microsoft Excel and Power BI that allows users to search, connect to, and transform data from a wide variety of sources. One such source is the Statistical Information System Collaboration Community (SIS-CC) SDMX Data Source. In this article, we will explore the Power Query M Language code needed to connect to the SIS-CC SDMX Data Source from inside Power BI.
What is the SIS-CC SDMX Data Source?
The SIS-CC SDMX Data Source is a repository of statistical data that has been collected and standardized by various international organizations. SDMX is a standard format for exchanging statistical data that is used by a range of organizations including the United Nations, the World Bank, Eurostat, and many others.
Why Use Power Query M Language Code?
While Power Query offers a user-friendly interface for connecting to data sources, it is often necessary to use M Language code to access specific data in a more efficient and precise manner. Using M Language code also allows for greater flexibility and customization in data transformation.
Connecting to the SIS-CC SDMX Data Source
To connect to the SIS-CC SDMX Data Source in Power BI, you will need to follow these steps:
1. Open Power BI and click on the ‘Get Data’ button on the Home tab.
2. In the ‘Get Data’ window, search for ‘Web’ and select ‘Web’ from the list of available connectors.
3. In the ‘Web’ settings window, enter the URL for the SIS-CC SDMX Data Source. This URL will vary depending on the specific data you wish to access, but it will generally follow this format: https://sdmx-siscc.unstats.unorg/ws/rest/data/{dataflow}/{version}/{resource}/{startPeriod}/{endPeriod}?{query}
4. Click on the ‘OK’ button to connect to the SIS-CC SDMX Data Source.
5. In the ‘Navigator’ window, select the specific data you wish to import into Power BI. You may need to enter additional parameters to refine your search.
6. Click on the ‘Load’ button to import the data into Power BI.
Power Query M Language Code for SIS-CC SDMX Data Source
If you need to access more detailed or complex data from the SIS-CC SDMX Data Source, it may be necessary to use M Language code. Here is an example of M Language code for connecting to the SIS-CC SDMX Data Source:
let
Source = Xml.Tables(Web.Contents(“https://sdmx-siscc.unstats.unorg/ws/rest/data/{dataflow}/{version}/{resource}/{startPeriod}/{endPeriod}?{query}”)),
Data = Source{0}[Table],
#”Promoted Headers” = Table.PromoteHeaders(Data, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}})
in
#”Changed Type”
This code uses the ‘Xml.Tables’ function to extract the data from the SIS-CC SDMX Data Source, and then uses various transformation functions to refine the data as needed.
Conclusion
The SIS-CC SDMX Data Source is a valuable repository of standardized statistical data, and Power Query provides a powerful means of accessing and manipulating this data. By using M Language code, users can customize their data transformation processes and gain greater control over the data they are working with. With the code provided in this article, you should now be able to connect to the SIS-CC SDMX Data Source from inside Power BI and begin exploring its vast array of data resources.
