@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_EXPRESSIONS
Then like the other answer in this thread, you can export the result to a file.
