Recently I found a PowerQuery gem, trick with Java/VB Script that allows to export data from Power Query to CSV without R / DAX Studio / SMS and Registration. However, related with risk. As everything else in our life.
Shi proposes to use following code (extended with my comments)
// reference to a query you wish to export to CSV
Source = ReferenceToYourTableOrQuery,
// demote headers to have headers in resulting CSV
// if you don't need headers, remove Table.DemoteHeaders
Json = Text.FromBinary(Json.FromValue(Table.ToRows(Table.DemoteHeaders(Source)))),
// trigger execution of script
Export = Web.Page("
var fso=new ActiveXObject('Scripting.FileSystemObject');
var arr=" & Json & ";
All great, but this method doesn’t work with default settings of Internet Explorer.
expression a variable that represents a Workbook object.
Data Type / Description
msoPBIExport = 0, publish workbook as a dataset. Workbook must contain Table Data (range formatted as a Table) or Data Model (aka PowerPivot model).
msoPBIUpload = 1 (default) publish workbook as a report, which can be consumed in Excel Online directly in PowerBI.
msoPBIAbort = 1 (default), workbook will not be loaded to PowerBI if there already exists workbook with same full name (including extension)
msoPBIIgnore = 0, ignores existence of report / dataset and creates new one on Upload/Export.
msoPBIOverwrite = 2, overwrites existing report / dataset with same full name of workbook. Note: method raises an error if find more than one report / dataset with same full name (e.g. if msoPBIIgnore was used previously).
Name of target Workspace.
If omitted, method loads workbook to “My Workspace” of signed-in user.
Note: requires Pro-account, signed-in user must have edit rights in target workspace.
' Creates Dataset from ActiveWorkbook in workspace "Finance Team" (if signed in user has access to it)
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, bstrGroupName:="Finance Team", PublishType:=msoPBIExport
' Creates PowerBI Report from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIUpload
' Creates Dataset from ActiveWorkbook in signed in user's workspace
ActiveWorkbook.PublishToPBI nameConflict:=msoPBIOverwrite, PublishType:=msoPBIExport