Difference between revisions of "Refreshable Excel data spreadsheets"

From Fusion Registry Wiki
Jump to navigation Jump to search
(→‎Private Fusion Registry data services and authentication)
 
(89 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
[[Category:How_To]]
 
[[Category:How_To]]
−
 
+
[[Category:Fusion Data Browser]]
 
= Overview =  
 
= Overview =  
 
Refreshable data spreadsheets can be created using Excel's standard data management features with the data being retrieved directly from Fusion Registry's CSV REST web services.
 
Refreshable data spreadsheets can be created using Excel's standard data management features with the data being retrieved directly from Fusion Registry's CSV REST web services.
  
−
== Principles ==
+
[[File:ExcelRefreshableSheet.PNG|thumb|Excel Refreshable Data Spreadsheet]]
−
Excel has built-in functionality for loading data from a range of sources including the Web which allows it to load data directly from Fusion Registry's REST API. The REST API can provide data in a number of different formats including XML and JSON, but CSV is the best choice being easily interpreted and processed by Excel.<br>
+
 
−
A simple refreshable sheet can be created with an Excel Web data query and a Fusion Registry REST API data query URL.<br>
+
= Principles =
 +
Excel has built-in functionality for loading data from a range of sources including the Web which allows data to be retrieved directly from Fusion Registry's REST API. The REST API can provide data in a number of different formats including XML and JSON, but CSV is the best choice being easily interpreted and processed by Excel.
 +
 
 +
A simple refreshable sheet can be created using a Fusion Registry GET data query URL as the source.<br>
 
<strong>Example</strong>
 
<strong>Example</strong>
 
<i>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv</i>
 
<i>https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv</i>
 
====Basic Steps====
 
====Basic Steps====
−
# Open a new Excel workbook and choose the Data menu.
+
# Open a new Excel workbook and choose the Data menu
−
# From the ribbon bar choose: New Query > From Other Sources > From Web
+
# From the ribbon bar choose New Query > From Other Sources > From Web
−
# Enter the Fusion Registry REST URL for the data you are interested in
+
# Enter the Fusion Registry GET data query URL for the data you are interested in
−
Excel should connect to the URL and download the data into the worksheet which can be saved and used as normal. The data can be refreshed as needed using the Refresh All on the Data ribbon bar. The Show Queries option, also on the Data ribbon bar will open the query sidebar with more information.
+
Excel will connect to the URL and download the data into the worksheet which can be saved and used as normal.<br>
−
 
+
The data can be refreshed as needed using the Refresh All option on the Data ribbon bar.<br>
−
== Generating the data URLs ==
+
The Show Queries option, also on the Data ribbon bar, opens the query sidebar with more information about the queries and data sources used in the workbook.
−
Fusion Registry's REST API follows the SDMX REST API syntax, with some additional parameters and CSV extensions which are useful here.
 
  
 +
= Generating Fusion Registry GET data query URLs =
 
URLs can be crafted by hand if you understand the SDMX REST syntax and know what datasets you need. However, there are a couple of other ways to discover data and generate the URLs automatically.
 
URLs can be crafted by hand if you understand the SDMX REST syntax and know what datasets you need. However, there are a couple of other ways to discover data and generate the URLs automatically.
 
====Fusion Data Browser====
 
====Fusion Data Browser====
−
# Launch the Fusion Data Browser
+
[[File:QuerySyntaxOption.PNG|thumb|Fusion Data Browser Query Syntax Function]]
−
# Choose a data set and use the quick filters to select the data of interest.
+
# Launch the Fusion Data Browser ([https://demo.metadatatechnology.com/DataBrowser/ demo])
−
# Choose Export to CSV, but rather than exporting the file use the Query Syntax function to get the URL
+
# Choose a data set and use the quick filters to select the data of interest
 +
# Export to CSV, but rather than exporting the data as a file choose the Query Syntax function which will display the URL
 
The URL for the query can be copied and pasted into Excel.
 
The URL for the query can be copied and pasted into Excel.
 +
 +
<strong>Note:</strong> Check that a GET query is generated. Complex data selections in Fusion Data Browser generate a HTTP POST which requires the Power Query Editor in Excel. There's more about this in the section on [[#GET_and_POST_queries|GET and POST queries]].
  
 
====Fusion Registry Web Services Query Builder====
 
====Fusion Registry Web Services Query Builder====
Line 31: Line 37:
 
The URL for the query can be copied and pasted into Excel.
 
The URL for the query can be copied and pasted into Excel.
  
−
== Private Fusion Registry data services and authentication ==
+
= Private Fusion Registry data services and authentication =
−
Excel will need to authenticate with the REST API if the Registry is operating in private mode or Content Security rules are in place to control who has access to what data.
+
Excel will need to authenticate with the REST API if the Registry is operating in private mode or Content Security rules are in place to control user access to data.
  
−
In private mode, Excel will detect authentication is required and will automatically prompt for a username and password. But in other cases, you may need to explicitly set the credentials that Excel will use when accessing the API.  
+
In private mode, Excel will detect authentication is required and automatically prompt for a username and password. In other cases, you may need to explicitly set the credentials that Excel will use when calling the API.  
 
# In Excel, after entering the URL, choose the Transform Data option on the data preview window - this will show the Power Query Editor
 
# In Excel, after entering the URL, choose the Transform Data option on the data preview window - this will show the Power Query Editor
 
# On the Power Query ribbon bar, choose the Data Source Setting option
 
# On the Power Query ribbon bar, choose the Data Source Setting option
Line 41: Line 47:
 
# Choose Basic and enter the User name and Password
 
# Choose Basic and enter the User name and Password
  
−
== GET and POST REST queries ==
+
<strong>Note:</strong> Excel only supports authentication on GET queries.
−
HTTP GET data queries largely follow the standard SDMX syntax and are suitable where regular a regular sub-cube from a single Dataflow is required. For more complex queries Fusion Registry provides a POST option which allows data to be retrieved from multiple Dataflows, and arbitrary selections of series which cannot be described using sub-cube syntax.
+
 
 +
= GET and POST queries =
 +
HTTP GET data queries largely follow the standard SDMX syntax and are suitable where a regular sub-cube is required from a single Dataflow. For more complex queries Fusion Registry provides a POST option which allows data to be retrieved from multiple Dataflows and arbitrary selections of series which cannot be described using the sub-cube syntax.
  
 
====GET queries====
 
====GET queries====
−
All of the queries described so far use the HTTP GET method which is the default assumed by Excel's Web data query.
+
Excel uses the HTTP GET method by default for web data sources.
  
−
The <em>format</em> parameter or the HTTP <em>Accept</em> header are used to specify the CSV format in which the data will be returned. While either are supported by Excel, the <em>format</em> parameter approach is recommended for simplicity. Use Excel's 'advanced' web query option to set the <em>Accept</em> header.
+
When creating a refreshable Excel sheet using the Fusion Registry REST API as the source, the <em>format</em> URL parameter or the HTTP <em>Accept</em> header must be set to specify the CSV data format. While both are supported by Excel, the <em>format</em> parameter approach is generally recommended for simplicity. Use Excel's 'advanced' web query option to set <em>Accept</em> and other headers.
 +
 
 +
Four different basic CSV formats are supported by Fusion Registry, all of which can be used in refreshable Excel data sheets.
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
! Format Parameter !! Accept Header !! Meaning !! Format Parameter Example
+
! Format !! Description !! URL Parameter !! Accept Header !! Format Parameter Example
 
|-
 
|-
−
| csv || application/vnd.csv || Flat format with one row per observation || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=both
+
| [[Fusion-CSV|Fusion CSV]] || Flat CSV with one observation per row || format=csv || application/vnd.csv || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv
 
|-
 
|-
−
| csv-ts|| application/vnd.csv-ts || Time Series format with one row per series and time periods across the columns || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-ts
+
| [[Fusion-CSV-TS|Fusion CSV Time Series]] || Time Series CSV with one series per row and time periods across the columns || format=csv-ts || application/vnd.csv-ts || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-ts
 
|-
 
|-
−
| csv-series || application/vnd.csv-series || Time Series format with one series per column and time periods down the rows || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-series
+
| [[Fusion-CSV-Series|Fusion CSV Vertical Time Series]] || Time Series CSV with one series per column || format=csv-series || application/vnd.csv-series || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-series
 +
|-
 +
| [[SDMX-CSV|SDMX CSV]] || The SDMX standard CSV format || format=sdmx-csv|| application/vnd.sdmx-csv || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=sdmx-csv
 
|}
 
|}
  
−
The csv-series option
+
====POST queries====
 +
[[File:PowerQueryMCode.PNG|thumb|Editing M Code in Power Query]]
 +
POST queries work by describing the query in JSON and sending that to the Fusion Registry REST API using the HTTP POST method.
 +
 
 +
In Excel, POST queries need to be coded in [https://docs.microsoft.com/en-us/powerquery-m/ Microsoft's Power Query M formular language] using the Power Query Editor advanced editing function.
 +
 
 +
The following Microsoft M code example selects nine specific series identified by their series keys from the ECB's Exchange Rate dataset. The JSON query is assigned to the <em>content</em> variable and passed as a parameter to the <em>Web.Contents()</em> function using the expression <em>Content=Text.ToBinary(content)</em>. The 'flat' CSV data format is used by setting the <em>Accept</em> header to <em>application/vnd.csv</em>.
 +
let
 +
content = "{
 +
      ""queries"": [{
 +
      ""obj"": ""DataQueryDef"",
 +
      ""series"": [
 +
        ""A:E5:PLN:ERD0:A"",
 +
        ""A:E5:PLN:ERC0:A"",
 +
        ""A:E5:MXN:EN00:A"",
 +
        ""A:E5:NOK:ERU1:A"",
 +
        ""M:CZK:EUR:SP00:A"",
 +
        ""M:CAD:EUR:SP00:A"",
 +
        ""A:CZK:EUR:SP00:A"",
 +
        ""A:CAD:EUR:SP00:E"",
 +
        ""A:SEK:EUR:SP00:E""],
 +
      ""dataflow"": {
 +
        ""id"": ""EXR"",
 +
        ""agencyId"": ""ECB"",
 +
        ""version"": ""1.0""
 +
      },
 +
      ""type"": ""series""}]
 +
    }",   
 +
    Source = Csv.Document(Web.Contents("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data", [Headers=[Accept="application/vnd.csv;labels=both", #"Content-Type"="application/json"],Content=Text.ToBinary(content)]
 +
    ),[Delimiter=",", Columns=31, Encoding=65001, QuoteStyle=QuoteStyle.None]),
 +
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
 +
in
 +
    #"Promoted Headers"
 +
 
 +
To build a refreshable spreadsheet using this code:
 +
# Create an new Excel worksheet and from the Data ribbon bar choose New Query > From Other Sources > Blank Query
 +
# The Power Query Editor should be displayed - in the Home ribbon bar choose Advanced Editor and paste in the M code
 +
Power Query should display a preview of the data. Choose Close and Load to complete the process and load the entire query result into the worksheet.
 +
 
 +
<strong>Note:</strong> POST queries will only work in Excel if the Fusion Registry API is public due to a restriction in Microsoft's Power Query engine.
 +
 
 +
=Generating the Microsoft M code for a POST query=
 +
====Fusion Data Browser generated POST query JSON====
 +
For complex queries, in particular where lists of specific series are chosen or when using [[Series Basket|Series Baskets]], Fusion Data Browser can generate the JSON needed for the M code.
 +
# Launch the Fusion Data Browser
 +
# Choose the required datasets and series using Series Baskets if needed
 +
# Choose Export to CSV, and choose the Query Syntax function which will display the POST URL and the JSON
 +
# Build the M code by hand setting the value of the <em>content</em> variable to the JSON, setting the URL of the Fusion Registry REST API in the <em>Web.Contents</em> function, and similarly setting the <em>Accept</em> header to the CSV data format required
 +
 
 +
<strong>Note:</strong> Double-quotes in the JSON need to be escaped in the M code by replacing each single " mark in the JSON with two marks: ""<br>
  
−
====POST queries====
+
Raw JSON
−
POST queries work by sending some JSON which describes the data required to the Fusion Registry REST API using the HTTP POST method.
+
"agencyId": "ECB"
 +
JSON in the M Code
 +
""agencyId"": ""ECB""
 +
 
 +
====Fusion Data Browser generated M code (forthcoming feature)====
 +
A function will be provided to automatically generate the complete M code needed for a query in a forthcoming release of the Fusion Data Browser.
 +
 
 +
=M code for automatically promoting the first row to headers=
 +
The M code example below can be used to pull any API data query directly into a refreshable Excel worksheet, automatically promoting the component labels in the first row to the headers. Just replace the the 'url' variable with your data GET query URL.
 +
 
 +
To execute, from the Excel ribbon bar:<br>
 +
Data > New Query > Blank Query > Advanced Editor<br>
 +
Paste the M code into the Advanced Editor box.<br>
 +
Choose 'Done', followed by 'Close and Load'.
 +
 
 +
let
 +
  url = "https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.HUF+MXN+MYR...?format=csv-ts",
 +
  Source = Csv.Document(Web.Contents(url),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
 +
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
 +
in
 +
  #"Promoted Headers"
 +
 
 +
=IDs and Labels=
 +
By default, Fusion Registry will return IDs only in data query results.
 +
 
 +
You can control whether the data returned to the worksheet includes IDs, Labels or both using additional URL parameters or modified [[Data_Formats#HTTP_Accept_Headers|HTTP Accept Headers]].
 +
 
 +
{| class="wikitable"
 +
|-
 +
! URL Parameter !! Meaning !! Example
 +
|-
 +
| labels=id || IDs only || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=id
 +
|-
 +
| labels=name || Names only || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=name
 +
|-
 +
| labels=both || Both names and IDs || https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=both
 +
|}
 +
 
 +
Adding a <em>labels</em> suffix to the <em>Accept</em> header achieves the same results:
 +
 
 +
{| class="wikitable"
 +
|-
 +
! Accept Header !! Meaning
 +
|-
 +
| application/vnd.csv;labels=both || Flat CSV (one row per observation) with separate columns for IDs and Names
 +
|-
 +
| application/vnd.csv-ts;labels=name || Time Series CSV (one row per series) with Names only
 +
|-
 +
| application/vnd.csv-series;labels=both || Vertical Time Series CSV (one column per series) with both IDs and Names
 +
|}

Latest revision as of 22:37, 10 September 2023

Overview

Refreshable data spreadsheets can be created using Excel's standard data management features with the data being retrieved directly from Fusion Registry's CSV REST web services.

Excel Refreshable Data Spreadsheet

Principles

Excel has built-in functionality for loading data from a range of sources including the Web which allows data to be retrieved directly from Fusion Registry's REST API. The REST API can provide data in a number of different formats including XML and JSON, but CSV is the best choice being easily interpreted and processed by Excel.

A simple refreshable sheet can be created using a Fusion Registry GET data query URL as the source.
Example https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv

Basic Steps

  1. Open a new Excel workbook and choose the Data menu
  2. From the ribbon bar choose New Query > From Other Sources > From Web
  3. Enter the Fusion Registry GET data query URL for the data you are interested in

Excel will connect to the URL and download the data into the worksheet which can be saved and used as normal.
The data can be refreshed as needed using the Refresh All option on the Data ribbon bar.
The Show Queries option, also on the Data ribbon bar, opens the query sidebar with more information about the queries and data sources used in the workbook.

Generating Fusion Registry GET data query URLs

URLs can be crafted by hand if you understand the SDMX REST syntax and know what datasets you need. However, there are a couple of other ways to discover data and generate the URLs automatically.

Fusion Data Browser

Fusion Data Browser Query Syntax Function
  1. Launch the Fusion Data Browser (demo)
  2. Choose a data set and use the quick filters to select the data of interest
  3. Export to CSV, but rather than exporting the data as a file choose the Query Syntax function which will display the URL

The URL for the query can be copied and pasted into Excel.

Note: Check that a GET query is generated. Complex data selections in Fusion Data Browser generate a HTTP POST which requires the Power Query Editor in Excel. There's more about this in the section on GET and POST queries.

Fusion Registry Web Services Query Builder

  1. From the Fusion Registry user interface, choose Web Service > Data from the left-hand menu bar
  2. Choose the Dataflow and the sub-cube using the query builder
  3. Choose the CSV data format

The URL for the query can be copied and pasted into Excel.

Private Fusion Registry data services and authentication

Excel will need to authenticate with the REST API if the Registry is operating in private mode or Content Security rules are in place to control user access to data.

In private mode, Excel will detect authentication is required and automatically prompt for a username and password. In other cases, you may need to explicitly set the credentials that Excel will use when calling the API.

  1. In Excel, after entering the URL, choose the Transform Data option on the data preview window - this will show the Power Query Editor
  2. On the Power Query ribbon bar, choose the Data Source Setting option
  3. Choose the new data source from the list and select the Edit Permissions option
  4. Under Credentials choose Edit...
  5. Choose Basic and enter the User name and Password

Note: Excel only supports authentication on GET queries.

GET and POST queries

HTTP GET data queries largely follow the standard SDMX syntax and are suitable where a regular sub-cube is required from a single Dataflow. For more complex queries Fusion Registry provides a POST option which allows data to be retrieved from multiple Dataflows and arbitrary selections of series which cannot be described using the sub-cube syntax.

GET queries

Excel uses the HTTP GET method by default for web data sources.

When creating a refreshable Excel sheet using the Fusion Registry REST API as the source, the format URL parameter or the HTTP Accept header must be set to specify the CSV data format. While both are supported by Excel, the format parameter approach is generally recommended for simplicity. Use Excel's 'advanced' web query option to set Accept and other headers.

Four different basic CSV formats are supported by Fusion Registry, all of which can be used in refreshable Excel data sheets.

Format Description URL Parameter Accept Header Format Parameter Example
Fusion CSV Flat CSV with one observation per row format=csv application/vnd.csv https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv
Fusion CSV Time Series Time Series CSV with one series per row and time periods across the columns format=csv-ts application/vnd.csv-ts https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-ts
Fusion CSV Vertical Time Series Time Series CSV with one series per column format=csv-series application/vnd.csv-series https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv-series
SDMX CSV The SDMX standard CSV format format=sdmx-csv application/vnd.sdmx-csv https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=sdmx-csv

POST queries

Editing M Code in Power Query

POST queries work by describing the query in JSON and sending that to the Fusion Registry REST API using the HTTP POST method.

In Excel, POST queries need to be coded in Microsoft's Power Query M formular language using the Power Query Editor advanced editing function.

The following Microsoft M code example selects nine specific series identified by their series keys from the ECB's Exchange Rate dataset. The JSON query is assigned to the content variable and passed as a parameter to the Web.Contents() function using the expression Content=Text.ToBinary(content). The 'flat' CSV data format is used by setting the Accept header to application/vnd.csv. 

let
content = "{
     ""queries"": [{
     ""obj"": ""DataQueryDef"", 
     ""series"": [
       ""A:E5:PLN:ERD0:A"", 
       ""A:E5:PLN:ERC0:A"", 
       ""A:E5:MXN:EN00:A"", 
       ""A:E5:NOK:ERU1:A"",
       ""M:CZK:EUR:SP00:A"",
       ""M:CAD:EUR:SP00:A"",
       ""A:CZK:EUR:SP00:A"",
       ""A:CAD:EUR:SP00:E"",
       ""A:SEK:EUR:SP00:E""],
     ""dataflow"": {
       ""id"": ""EXR"",
       ""agencyId"": ""ECB"",
       ""version"": ""1.0""
     },
     ""type"": ""series""}]
   }",    
   Source = Csv.Document(Web.Contents("https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data", [Headers=[Accept="application/vnd.csv;labels=both", #"Content-Type"="application/json"],Content=Text.ToBinary(content)]
   ),[Delimiter=",", Columns=31, Encoding=65001, QuoteStyle=QuoteStyle.None]),
   #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
   #"Promoted Headers"

To build a refreshable spreadsheet using this code:

  1. Create an new Excel worksheet and from the Data ribbon bar choose New Query > From Other Sources > Blank Query
  2. The Power Query Editor should be displayed - in the Home ribbon bar choose Advanced Editor and paste in the M code

Power Query should display a preview of the data. Choose Close and Load to complete the process and load the entire query result into the worksheet.

Note: POST queries will only work in Excel if the Fusion Registry API is public due to a restriction in Microsoft's Power Query engine.

Generating the Microsoft M code for a POST query

Fusion Data Browser generated POST query JSON

For complex queries, in particular where lists of specific series are chosen or when using Series Baskets, Fusion Data Browser can generate the JSON needed for the M code.

  1. Launch the Fusion Data Browser
  2. Choose the required datasets and series using Series Baskets if needed
  3. Choose Export to CSV, and choose the Query Syntax function which will display the POST URL and the JSON
  4. Build the M code by hand setting the value of the content variable to the JSON, setting the URL of the Fusion Registry REST API in the Web.Contents function, and similarly setting the Accept header to the CSV data format required

Note: Double-quotes in the JSON need to be escaped in the M code by replacing each single " mark in the JSON with two marks: ""

Raw JSON 

"agencyId": "ECB"

JSON in the M Code 

""agencyId"": ""ECB""

Fusion Data Browser generated M code (forthcoming feature)

A function will be provided to automatically generate the complete M code needed for a query in a forthcoming release of the Fusion Data Browser.

M code for automatically promoting the first row to headers

The M code example below can be used to pull any API data query directly into a refreshable Excel worksheet, automatically promoting the component labels in the first row to the headers. Just replace the the 'url' variable with your data GET query URL.

To execute, from the Excel ribbon bar:
Data > New Query > Blank Query > Advanced Editor
Paste the M code into the Advanced Editor box.
Choose 'Done', followed by 'Close and Load'. 

let
  url = "https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/ECB,EXR,1.0/A.HUF+MXN+MYR...?format=csv-ts",
  Source = Csv.Document(Web.Contents(url),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
  #"Promoted Headers"

IDs and Labels

By default, Fusion Registry will return IDs only in data query results.

You can control whether the data returned to the worksheet includes IDs, Labels or both using additional URL parameters or modified HTTP Accept Headers.

URL Parameter Meaning Example
labels=id IDs only https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=id
labels=name Names only https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=name
labels=both Both names and IDs https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv&labels=both

Adding a labels suffix to the Accept header achieves the same results:

Accept Header Meaning
application/vnd.csv;labels=both Flat CSV (one row per observation) with separate columns for IDs and Names
application/vnd.csv-ts;labels=name Time Series CSV (one row per series) with Names only
application/vnd.csv-series;labels=both Vertical Time Series CSV (one column per series) with both IDs and Names
Retrieved from "https://wiki.sdmxcloud.org/index.php?title=Refreshable_Excel_data_spreadsheets&oldid=6012"