Forum Discussion
How to Export Query in Power BI Desktop as CSV
- 7 years ago
Hi Anonymous,
Based on my research, in query editor, there is not a function to output a csv file in Power BI, I suggest you to try below solutions:
1.You could copy the table you want to export in data view after you have applied your queries.
Reference:https://community.powerbi.com/t5/Desktop/How-to-export-some-huge-query-results-in-excel/td-p/200084
2.You could use the Dax Studio to export the file you want.
Rference:https://community.powerbi.com/t5/Desktop/Export-to-csv-from-Power-Query/td-p/193423
Regards,
Daniel He
Anonymous Just want to make it clear, you mean to export the M-code in "Advanced Editor" ? If that is the case, there is no such option. Please post the screenshot which you are referring to ?
PattemManohar This is an old thread and not what the OP was looking for, but there are a bunch of ways to export the M-code:
- Just select and copy the query or queries from the list of queries in Excel or Power BI Desktop and paste them anywhere. You don't have to open the Advanced Editor. It really is that simple. You can even paste directly into another Excel or Power BI Desktop file. If you paste them somewhere for documentation purposes (eg. Word, OneNote, Notepad, etc.), the formatting is quite a bit different depending on where you paste them. I prefer the format when you paste into Notepad because it is more readable.
- In Excel, you can export queries via VBA macros. Here is the one I use:
Sub GetAllQueries()
'This will get all Power Query queries in this workbook and save them to a sheet named "PowerQuery".
'This will attempt to delete the sheet first and then create it
Dim q As Queries
Dim i As Integer
Dim s As Worksheet
'Delete sheet named "PowerQuery" if it exists
On Error Resume Next 'turn off error checking
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("PowerQuery").Select
ActiveWorkbook.Sheets("PowerQuery").Delete
Application.DisplayAlerts = True
On Error GoTo 0 'turn error checking back on
'Add sheet named "PowerQuery"
Set s = ActiveWorkbook.Sheets.Add()
s.Name = "PowerQuery"
'Loop through all queries and add them to the "PowerQuery" sheet
'Column A = Query name, Column B = Query "formula" (the query itself", Column C = Query description (Often blank. You can set desc from the All Properties button on the Query Settings pane.)
Application.ScreenUpdating = True
For i = 1 To ActiveWorkbook.Queries.Count
'Show progress on the status bar (lower left corner)
'If there are a lot of queries, this helps show progress
Application.StatusBar = "Fetching query " & i & " of " & ActiveWorkbook.Queries.Count
DoEvents 'Without this, the Status Bar updates won't show
'Write query info to sheet, leave top row blank (i+1) for headers
s.Cells(i + 1, 1).Value = ActiveWorkbook.Queries(i).Name 'Column A (1)
s.Cells(i + 1, 2).Value = ActiveWorkbook.Queries(i).Formula 'Column B (2)
s.Cells(i + 1, 3).Value = ActiveWorkbook.Queries(i).Description 'Column C (3)
Next
'Clear status bar
Application.StatusBar = False
'Format the sheet
'Resize columns
s.Columns("A:A").EntireColumn.AutoFit
If s.Columns("A:A").ColumnWidth < 28 Then s.Columns("A:A").ColumnWidth = 28
s.Columns("B:C").ColumnWidth = 80
'Align & wrap text
s.Columns("A:C").Select
With Selection
.VerticalAlignment = xlTop
.WrapText = True
End With
'Add headers
s.Range("A1").Value = "Query name"
s.Range("B1").Value = "Formula"
s.Range("C1").Value = "Description"
Range("B2").Select
ActiveWindow.FreezePanes = True
'Add refresh button
ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, 80, 0, 60, 15).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Refresh"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 7).Font
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Size = 11
End With
With Selection.ShapeRange.Fill
.ForeColor.ObjectThemeColor = msoThemeColorAccent4
End With
With Selection.ShapeRange.TextFrame2
.TextRange.ParagraphFormat.Alignment = msoAlignCenter
.VerticalAnchor = msoAnchorMiddle
.TextRange.Font.Bold = msoTrue
End With
Selection.OnAction = "GetAllQueries"
'Add table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1", Cells.SpecialCells(xlCellTypeLastCell)), , xlYes).Name = "PowerQueries"
Range("A1").Select
End Sub- For Power BI Desktop, use DAX Studio with the following query:
select [Name], [Expression], [Description], [ModifiedTime] from $SYSTEM.TMSCHEMA_EXPRESSIONSThen like the other answer in this thread, you can export the result to a file.