Refreshable Excel data spreadsheets - Revision history

Vmurrell at 14:51, 12 October 2025

2025-10-12T14:51:22Z

2023-09-11T06:37:36Z

2020-10-20T16:15:10Z

‎M code for automatically promoting the first row to headers

2020-10-20T16:14:49Z

‎M code for automatically promoting the first row to headers

2020-10-20T16:14:23Z

‎M code for automatically promoting the first row to headers

2020-10-20T16:12:03Z

2020-06-25T08:39:48Z

‎POST queries

2020-06-25T08:39:22Z

‎POST queries

2020-06-25T08:37:53Z

‎POST queries

2020-06-25T08:36:08Z

‎Private Fusion Registry data services and authentication

← Older revision		Revision as of 14:51, 12 October 2025
Line 1:		Line 1:
−	~~[[Category:How_To]]~~
	[[Category:Fusion Data Browser]]		[[Category:Fusion Data Browser]]
	= Overview =		= Overview =

@@ Line 1: / Line 1: @@
 [[Category:How_To]]
+[[Category:Fusion Data Browser]]
 = 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.

@@ Line 136: / Line 136: @@
 Data > New Query > Blank Query > Advanced Editor<br>
 Paste the M code into the Advanced Editor box.<br>
-Choose 'Done', the 'Close and Load'.
+Choose 'Done', followed by 'Close and Load'.
   let

@@ Line 133: / Line 133: @@
 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:
+To execute, from the Excel ribbon bar:<br>
-Data > New Query > Blank Query > Advanced Editor
+Data > New Query > Blank Query > Advanced Editor<br>
-Paste the M code into the Advanced Editor box.
+Paste the M code into the Advanced Editor box.<br>
 Choose 'Done', the 'Close and Load'.

@@ Line 131: / Line 131: @@
 =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
+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.
   let

@@ Line 129: / Line 129: @@
 ====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.
 =IDs and Labels=

@@ Line 76: / Line 76: @@
 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>.
@@ Line 111: / Line 109: @@
 # 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.
 =Generating the Microsoft M code for a POST query=

@@ Line 77: / Line 77: @@
 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.
-<strong>Note:</strong> Excel only supports POST queries with anonymous authentication. This means they can only be used if the Fusion Registry API is public.
+<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.
 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>.

← Older revision		Revision as of 08:37, 25 June 2020
Line 76:		Line 76:

	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.		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.
		+
		+	<strong>Note:</strong> Excel only supports POST queries with anonymous authentication. This means they can only be used if the Fusion Registry API is public.

	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>.		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>.

@@ Line 47: / Line 47: @@
 # Choose Basic and enter the User name and Password
-<strong>Note:</strong> Excel only supports authentication on GET queries. POST queries will only work if the Fusion Registry API is public.
+<strong>Note:</strong> Excel only supports authentication on GET queries.
 = GET and POST queries =