none
Extract query text to create documentation

    Question

  • I need to document the design of a workbook that contains around 20 Power Queries. I would like the documentation to contain the formulas of each query. 

    I have found no other way than manually opening the advanced editor window of each query, copying and pasting the text into the document. And when you update a query, you have to go through this again. It's a pain !

    Unfortunately, the connection string of the workbook connections are encoded: Vba is of no help here.  

    Any other solution ? 

    Regards,

    Bertrand♥

    Tuesday, January 21, 2014 8:49 AM

Answers

  • Hi guys,

    Thanks for the feedback. We don't have a straightforward way of doing this today.

    A little trick that I personally use a lot is, rather than copy/paste the source query by query, use the Send a Frown button and "Include Formulas" in the generated email.

    This gives you the M source for the current section (section 1). As you may guess there is no support for creating new sections within a workbook either.

    Hope this helps.

    M.

    Tuesday, January 21, 2014 8:55 PM
    Owner
  • Hi Chris,

    We acknowledge that VBA OM additions for Power Query (create query, remove query, modify query source, etc.) would be very useful in many customer scenarios and have this feature in our roadmap. That said, we can't share any specific timelines (so you may infer that this isn't something that will be available in the short-term).

    Thanks for the feedback.

    M.

    Thursday, January 23, 2014 4:51 AM
    Owner

All replies

  • I've been looking at how to do this too - it would be very useful. I thought using #sections would give me the ability to do this but I get a Formula.Firewall error whenever I navigate to the record that it returns.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, January 21, 2014 9:58 AM
  • Hi guys,

    Thanks for the feedback. We don't have a straightforward way of doing this today.

    A little trick that I personally use a lot is, rather than copy/paste the source query by query, use the Send a Frown button and "Include Formulas" in the generated email.

    This gives you the M source for the current section (section 1). As you may guess there is no support for creating new sections within a workbook either.

    Hope this helps.

    M.

    Tuesday, January 21, 2014 8:55 PM
    Owner
  • Yes, I found that. So just to confirm, every M expression is always in section 1? Is there any use at all for #sections in Power Query or is it just something over from the past?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, January 21, 2014 10:02 PM
  • Is it expected/probable that when released that there will be VBA object model additions related to Power Query?

     - Chris

    Thursday, January 23, 2014 2:41 AM
  • Hi Chris,

    We acknowledge that VBA OM additions for Power Query (create query, remove query, modify query source, etc.) would be very useful in many customer scenarios and have this feature in our roadmap. That said, we can't share any specific timelines (so you may infer that this isn't something that will be available in the short-term).

    Thanks for the feedback.

    M.

    Thursday, January 23, 2014 4:51 AM
    Owner
  • For me enabling Fast Combine didn't work, but you can actually check the contents of #sections (or of #shared for that matter), though you'd better do so in a new query-less workbook (lest Formula.Firewall forces you to mention all your other queries):

    let
        DefeatFirewall = {MyQueryName1, AndAnother, MyLastQuery}
    in
        #sections


    Saturday, June 21, 2014 11:55 AM
  • Has anyone developped a macro / power query to format the output of the generated email text ? 
    Wednesday, December 17, 2014 10:34 AM
  • I have finally resolved to writing it myself! Below is the VBA code that I have inserted in an outlook code module. I also added a button in the message quick access toolbar to invoke the macro.

    1. Click "send a frown button" as suggested by Miguel above. In my case, this creates a new Outlook message
    2. Run the macro below. It creates  a new Word document with Name, description (from the query properties --do not leave empty !! -- ) and code.

    Note that you will need to set a reference to "Microsoft VBScript regular expressions 5.5" library.

    Sub Format_M_code()
        Dim objOL As Outlook.Application
        Dim objDoc As Word.Document
        Dim objRng As Word.Range
        Dim ExportedMcode As String, SearchPattern As String
        On Error Resume Next
        Set objOL = Application

        If objOL.ActiveInspector.EditorType = olEditorWord Then
            Set objDoc = objOL.ActiveInspector.WordEditor
            Set objRng = objDoc.Range
            With objRng.TextRetrievalMode
                .IncludeHiddenText = False
                .IncludeFieldCodes = False
            End With
            ExportedMcode = objRng.Text

            SearchPattern = "(?:\[\s+Description\s+=\s+"")(.*?)""\s\](?:.?shared\s)(.*?)=\s(.*?);"
            regex_search SearchPattern, Trim(ExportedMcode)
        End If
        Set objTbl = Nothing
        Set objOL = Nothing
        Set objNS = Nothing
    End Sub
    Sub regex_search(pattrn, str_to_search)
        Dim regEx As RegExp

        Dim wrdApp As Word.Application
        Dim wrdDoc As Word.Document
        Dim wrdPar As Word.Paragraph
        Dim objSelection As Word.Selection

        Dim i As Integer: i = 0
        Dim Matches As MatchCollection
        Dim omatch As Match
        Dim subm As SubMatches
        Dim subm_cnt As Integer
        Dim pval()

        Set regEx = New RegExp
        With regEx
            .Pattern = pattrn
            .IgnoreCase = False
            .Global = True
            .MultiLine = False
        End With
        Err.Clear
        On Error Resume Next
        Set Matches = regEx.Execute(str_to_search)   ' Execute search.
        Debug.Print Matches.Count
        If Matches.Count > 0 Then

            'Create empty word document
            Set wrdApp = CreateObject("Word.Application")
            wrdApp.Visible = True
            Set wrdDoc = wrdApp.Documents.Add
            objWord.Visible = True
            Set objSelection = wrdApp.Selection

            'Create new style for the code sections
            wrdDoc.Styles.Add Name:="MCode", Type:=wdStyleTypeParagraph
            With wrdDoc.Styles("MCode")
                .Font.Name = "Consolas"
                .Font.Size = 8
                .NoProofing = True
            End With

            'Insert section header
            With objSelection
                .Style = wrdDoc.Styles("Heading 1")
                .TypeText ("Queries")
                .TypeParagraph
            End With


            For Each omatch In Matches
                Set subm = omatch.SubMatches
                'submatch #0 = query description
                'submatch #1 = query name
                'submatch #2 = query code

                With objSelection
                    .Style = wrdDoc.Styles("Heading 2")
                    .TypeText subm.Item(1)
                    .TypeParagraph
                    .Style = wrdDoc.Styles("Heading 3")
                    .TypeText "Description"
                    .TypeParagraph
                    .Style = wrdDoc.Styles("Normal")
                    .TypeText subm.Item(0)
                    .TypeParagraph
                    .Style = wrdDoc.Styles("Heading 3")
                    .TypeText "Code"
                    .TypeParagraph
                    .Style = wrdDoc.Styles("Mcode")
                    .TypeText subm.Item(2)
                    .TypeParagraph
                End With
            Next
        End If
    End Sub

            


    Friday, December 19, 2014 1:40 PM
  • I don't know if the feature was available back then, but I find it easier to select the queries I want to document in the Query Navigation Pane and copy them (either CTRL+C or right click -> Copy).

    It not only copies section member names and expressions, but also its properties.  That is very practical when copying functions from one document to another.

    Friday, February 24, 2017 10:33 PM
  • No, copy/paste wasn't available back then. But I didn't realise that you could copy a query and then paste into a text editor and get the text of the query, so thanks for pointing that out!

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Saturday, February 25, 2017 7:23 PM