Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community,
Can someone let me know how to export a query generated from the Query Editor to a CSV file?
Thanks
Carlton
Solved! Go to Solution.
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
Hi @Anonymous,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
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 ?
Proud to be a PBI Community Champion
@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:
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
select [Name], [Expression], [Description], [ModifiedTime] from $SYSTEM.TMSCHEMA_EXPRESSIONS
Then like the other answer in this thread, you can export the result to a file.
So, pattemmanohar, the original .csv was uploaded into Power BI and edited with Query Editor. I would now like export the edited .csv file back with the edits made with Query Editor.
Can you let me know how that is accomplished/
Cheers
Hi Pattemanohar,
Thanks for responding.
I would like to export/copy the query from the screen shot into a .csv
Thanks
I often deal with repetitive queries, so I need to handle it by exporting to view as well as paste it into other datasets.
Did you try this way?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |