none
Query Peek Window Data Source list - VBA RRS feed

  • Question

  • In Excel O365 subscription 2019:  In the Get & Transform data Query & Connection window, I can hover over a Query name or click the "Show the peek" link to open the Peek-window. Near the bottom of that window is a section labeled Data Source.  I'm using VBA to create a list of Queries on a sheet in my Workbook. I can't find the VBA object(s) that reproduces the Data Sources list. I've tried VBA Queries object, WorkbookConnections, Connections, ... without success.  I've investigates CommandType and CommandText.  It is not clear if the Data Sources are related to a Query or to a Workbook, but some queries will have the same source list while others will be different and some queries have no sources listed.  Any suggestions would be appreciated.

    elc


    • Edited by GeneQuery2 Tuesday, October 22, 2019 10:04 AM
    Tuesday, October 22, 2019 9:53 AM

Answers

  • Imke,

    I tired replacing the Table.FromRecords(...) with false but the error returned.  I guess the function needs to return a two-column table.

    Thanks for your help.


    elc

    • Marked as answer by GeneQuery2 Saturday, October 26, 2019 4:57 PM
    Saturday, October 26, 2019 4:56 PM

All replies

  • IIRC the information held in the Data Source configuration in Power Query is not exposed to VBA, only the queries themselves. This is mostly used for storing credentials to external data sources, which is probably why it's not accessible through VBA.

    So it's worth noting that if your goal was just to compile the "data source" of every query (as in, the actual source of data), you could generally just pull the "Source" line from the query code (unless you have a query with handwritten code where this isn't the case).

    Tuesday, October 22, 2019 3:40 PM
  • Thank you very much for your reply.  My objective is to manage my workbooks that contain queries.  A number of these books reference the same "data sources", either folders,  text files, or tables/queries in other or the current Excel workbook.  The peek window identifies "Data Sources" that are not contained in the query source line.  For example:  The source line of query "ORA_H_2018Gen1106" referrers to two other queries: "Source = Table.Combine({H_FieldNames, ORA_H_20181106}),".  The Data Sources section of the peek displays correctly the two sources referred to in each of the two queries: "c:\users\owner\onedrive\fl_voter_data\voterfilelayout.xlsx" and  "c:\users\owner\onedrive\fl_voter_data\ora\reg" respectively.  I have another query ,"PctVoted", whose source statement is: Source = Excel.CurrentWorkbook(){[Name="VotersAndVoted"]}[Content], and the Peek states "There is no data source for this query".  Its source is a named-table on a worksheet.

    Perhaps the meaning of the section "Data Sources" really is something like - files and folders upon which this query depends. If I could have program VBA to grab the Data Source list of file and folder paths I would have been happy.  By the way, I do merges in queries other than at the 1st line.

    To manage my workbooks I really want a list of the queries whose source lines, joins, and combines directly reference files and/or folders.  That way when I need to modify the interface I can see the list of all queries that refer to the same files/folders. 

    In VBA there is a QueryTable.SourceDataFile property that seems like it ought/might provide the info I desire.  The .CommandType property even suggests that it might be a list of .SourceDataFile paths.

    I certainly got lost in the Queries objects, ListTables, QueryTables, Connections and WorkbooKConnections Objects.  

    I appreciate your consideration of my question.


    elc

    Tuesday, October 22, 2019 6:52 PM
  • Hi GeneQuery2,

    you can use some M-code to grab the M-code from a file: https://www.thebiccountant.com/2019/03/06/bulk-extract-power-query-m-code-from-multiple-excel-files-at-once/

    This can also be used to read the M-code of the current file.

    Then do some text extraction to retrieve the file paths.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, October 26, 2019 7:21 AM
    Moderator
  • Hi Imke,

    Thank you very much for sharing your code to list M-queries.  I have a column of file paths that I use to create a query from table.  Then the next step is to added a custom column that invoke your function using the file path field as the input.  This works fine until I encounter a .xlsx-file that does not contain any queries.  

    Could you please suggest how to best accommodate this situation?

    Also, I have some .xlsm-files that include queries as well as macros.  I suppose just changeing the extension will work.  Is there a better solution?

    Again, Thank you very much.

    Gene


    elc

    Saturday, October 26, 2019 12:40 PM
  • Hi GeneQuery2,

    you can wrap the function call in an error-handler like so:

    try <FunctionCall> otherwise false

    then filter out false on that column


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, October 26, 2019 1:53 PM
    Moderator
  • Imke,  I still get this error when the first file without queries is processed:

    Expression.Error: There weren't enough elements in the enumeration to complete the operation.
    Details:
        [Table]

    Thhs is my code.  [FilePath] is a column of path names to .xlsx files.

    let
        Source = Excel.CurrentWorkbook(){[Name="General2018"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilePath", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  try each  func([FilePath]) otherwise false),
        #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "QueryCode"}, {"Name", "QueryCode"})
    in
        #"Expanded Custom1"

    Thank you.


    elc

    Saturday, October 26, 2019 3:02 PM
  • You have to make a slight modification in step #"Added Custom":

    let
        Source = Excel.CurrentWorkbook(){[Name="General2018"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilePath", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",  each try func([FilePath]) otherwise false),
        #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "QueryCode"}, {"Name", "QueryCode"})
    in
        #"Expanded Custom1" 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, October 26, 2019 3:14 PM
    Moderator
  • I get the same result after moving the "try" after the "each".

    It is not clear to me which enumeration the error is referencing.  I did notice there is some error processing in the unzip section.


    elc

    Saturday, October 26, 2019 4:15 PM
  • sorry, didn't realize where the error was located actually.

    I've added some error-handling in my function, please check if that works for your files:

    let func =   
     (FileOrFolderPath as text) =>
    
    let
    
    CreateTable = if Text.End(FileOrFolderPath,5) = ".xlsx" 
    then #table({"Content", "Name"}, {{File.Contents(FileOrFolderPath), FileOrFolderPath}}) 
    else Folder.Files(FileOrFolderPath) ,
    FetchQueries = Table.AddColumn(CreateTable, "FetchQueries", each try fnFetchQueries([Content]) otherwise #table({"Column1"}, {{null}})),
        #"Removed Other Columns" = Table.SelectColumns(FetchQueries,{"Name", "FetchQueries"}),
        #"Expanded FetchQueries" = Table.ExpandTableColumn(#"Removed Other Columns", "FetchQueries", {"Column1"}, {"QueryCode"}),
    
    // Helper functions    
    
    // Fetch queries from [Content]-column
    fnFetchQueries = (Source as binary) =>
    let 
        UnzipFile = fnUnzipFile(Source),
        // The filename where the queries reside is not known in beforehand. Just that it contains "customXml/item"
        FilterItemFiles = Table.SelectRows(UnzipFile, each Text.StartsWith([FileName], "customXml/item") and not Text.Contains([FileName], "Props")),
        FetchQueriesFromBinary = Table.AddColumn(FilterItemFiles, "fnGetQueriesFromBinary", each fnGetQueriesFromBinary([Content])),
        RemoveErrorRows = Table.RemoveRowsWithErrors(FetchQueriesFromBinary, {"fnGetQueriesFromBinary"}){0}[fnGetQueriesFromBinary]
    in
        RemoveErrorRows,
    
    
    // Extracts the queries from the binary
    fnGetQueriesFromBinary = (GrabItem1 as binary) =>
    let
        ParseAsXml = Xml.Tables(GrabItem1,null,1252),
        GrabText = ParseAsXml{0}[#"Element:Text"],
        BinaryFromText = Binary.FromText(GrabText, BinaryEncoding.Base64),
        UnzipSection1 = fnUnzipBinary(BinaryFromText, "Formulas/Section1.m"),
        TransformBinaryToList = Lines.FromBinary(UnzipSection1),
        ConvertToTable = Table.FromList(TransformBinaryToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        ConvertToTable,
    
    // Unzips the xlsx
    fnUnzipFile = (ZIPFile) => 
    let
        Header = BinaryFormat.Record([
            MiscHeader = BinaryFormat.Binary(14),
            BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
            ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)    
        ]),
    
        HeaderChoice = BinaryFormat.Choice(
            BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
            each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
                then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
                else BinaryFormat.Choice(
                        BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                        each BinaryFormat.Record([
                            IsValid  = true,
                            Filename = BinaryFormat.Text(Header(_)[FileNameLen]), 
                            Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]), 
                            Content  = BinaryFormat.Transform(
                                BinaryFormat.Binary(Header(_)[BinarySize]),
                                (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                            )
                            ]),
                            type binary                   // enable streaming
                    )
        ),
    
        ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
    
        Entries = List.Transform(
            List.RemoveLastN( ZipFormat(ZIPFile), 1),
            (e) => [FileName = e[Filename], Content = e[Content] ]
        )
    in
        Table.FromRecords(Entries),
    
    //Unzips the binary content
    fnUnzipBinary = (binaryZip,fileName) =>
    let
    //shorthand
        UInt32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),
        UInt16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),
    //ZIP file header fixed size structure
        Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
                    CompressedSize   = UInt32,
                    UncompressedSize = UInt32,
                    FileNameLen      = UInt16,
                    ExtraFieldLen = UInt16]),
    //ZIP file header dynamic size structure
        FileData = (h)=> BinaryFormat.Record([
                    FileName         = BinaryFormat.Text(h[FileNameLen]),
                    ExtraField       = BinaryFormat.Text(h[ExtraFieldLen]),
                    UncompressedData = BinaryFormat.Transform(
        BinaryFormat.Binary(h[CompressedSize]),
        (x) =>  try 
        Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) 
        otherwise null)]),
    //Parsing the binary in search for PKZIP header signature
        ZipIterator = BinaryFormat.Choice(UInt32, (signature) => if signature <> 0x04034B50 
                                                then BinaryFormat.Record([FileName=null])
                                                else BinaryFormat.Choice(Header,(z)=>FileData(z))),
        ZipFormat = BinaryFormat.List(ZipIterator),
        out = List.Select(ZipFormat(binaryZip), each _[FileName]=fileName)
    in
        out{0}[UncompressedData]
    
    in
        #"Expanded FetchQueries" ,
    documentation = [
    Documentation.Name =  " Xlsx.ExtractQueries ",
    Documentation.Description = " Extracts all queries from files in folder or xlsx-files ",
    Documentation.LongDescription = " Extracts all queries from files in folder or xlsx-files ",
    Documentation.Category = " Table ",
    Documentation.Source = " www.TheBIccountant.com .  https://wp.me/p6lgsG-112 . ",
    Documentation.Version = " 2.0 ",
    Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://wp.me/p6lgsG-112 . ",
    Documentation.Examples = {[Description =  "  ",
    Code = "  ",
    Result = "  "]}]
      
     in  
      Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, October 26, 2019 4:29 PM
    Moderator
  • I modified your code in as shown below.  See Bold highlight  This seems to overcome the error but it is probably not the best way.  I did see an error message flash by.  I'll try replacing the Table.FromRecords with false.

    let func =   
     (FileOrFolderPath as text) =>

    let

    CreateTable = if Text.End(FileOrFolderPath,5) = ".xlsx" 
    then #table({"Content", "Name"}, {{File.Contents(FileOrFolderPath), FileOrFolderPath}}) 
    else Folder.Files(FileOrFolderPath) ,
    FetchQueries = Table.AddColumn(CreateTable, "FetchQueries", each try fnFetchQueries([Content]) otherwise Table.FromRecords({[Filename = false, Content = false]})),
        #"Removed Other Columns" = Table.SelectColumns(FetchQueries,{"Name", "FetchQueries"}),
        #"Expanded FetchQueries" = Table.ExpandTableColumn(#"Removed Other Columns", "FetchQueries", {"Column1"}, {"QueryCode"}),

    // Helper functions    

    // Fetch queries from [Content]-column
    fnFetchQueries = (Source as binary) =>
    let 
        UnzipFile = fnUnzipFile(Source),
        // The filename where the queries reside is not known in beforehand. Just that it contains "customXml/item"
        FilterItemFiles = Table.SelectRows(UnzipFile, each Text.StartsWith([FileName], "customXml/item") and not Text.Contains([FileName], "Props")),
        FetchQueriesFromBinary = Table.AddColumn(FilterItemFiles, "fnGetQueriesFromBinary", each 
            try fnGetQueriesFromBinary([Content]) otherwise Table.FromRecords({[Filename = false, Content = false]})),
        RemoveErrorRows = Table.RemoveRowsWithErrors(FetchQueriesFromBinary, {"fnGetQueriesFromBinary"}){0}[fnGetQueriesFromBinary]
    in
        RemoveErrorRows,


    elc

    Saturday, October 26, 2019 4:42 PM
  • Imke,

    I tired replacing the Table.FromRecords(...) with false but the error returned.  I guess the function needs to return a two-column table.

    Thanks for your help.


    elc

    • Marked as answer by GeneQuery2 Saturday, October 26, 2019 4:57 PM
    Saturday, October 26, 2019 4:56 PM
  • Thanks for the update with error handling.

    I've tried it.  I get a different answer from your solution than I get from mine.  My solution loads 12,741 rows while yours loads 13,994 rows,  I appended the two queries then tried to remove duplicates but I received  the following error:

    Formula.Firewall: Query 'General2018 (5)' (step 'Expanded Custom') used 'MultipleUnclassified/Trusted' data when last evaluated, but now is attempting to use 'MultipleUnclassified/Trusted' data.

    So I created two new queries, one from each results table, then appended and removed duplicate rows.  This resulted in loading a table of 176 rows. That doesn't make any sense to me.   It will take a little time to sort through which is correct.  I may get to do that tomorrow.

    Again thinks for your help.


    elc

    Saturday, October 26, 2019 5:47 PM