Import All Fields in a Power Query

[Hashiru]

Hello All,

How do I import every/all fields in my query? I need the equivalent of SELECT FROM * . Below is what I got:

Sub CSVFiles()
Dim MyFile As FileDialog
Set MyFile = Application.FileDialog(msoFileDialogFilePicker)


Dim StrFileName As String, strPath As String
StrFileName = Mid(CSVFilepath, InStrRev(CSVFilepath, "\", -1) + 1, Len(CSVFilepath) - InStrRev(CSVFilepath, "\", -1))
strPath = Left(CSVFilepath, InStrRev(CSVFilepath, "\", -1) - 1)
' CSVFiles
    ActiveWorkbook.Queries.Add Name:="CSVFolderPivotTable", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Folder.Files(""" & strPath & """)," & Chr(13) & "" & Chr(10) & _
        "    #""Filtered Rows"" = Table.SelectRows(Source, each ([Name] = """ & StrFileName & """))," & Chr(13) & "" & Chr(10) & _
        "    #""Invoke Custom Function1"" = Table.AddColumn(#""Filtered Rows"", ""Transform File from CSVFolderPivotTable"", each #""Transform File from CSVFolderPivotTable""([Content]))," & Chr(13) & "" & Chr(10) & _
        "    #" & _
        """Renamed Columns1"" = Table.RenameColumns(#""Invoke Custom Function1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & "    #""Removed Other Columns1"" = Table.SelectColumns(#""Renamed Columns1"", {""Source.Name"", ""Transform File from CSVFolderPivotTable""})," & Chr(13) & "" & Chr(10) & "    #""Expanded Table Column1"" = Table.ExpandTableColumn(#""Removed Other Columns1"", ""Transform File from CSVFolderPiv" & _
        "otTable"", Table.ColumnNames(#""Transform File from CSVFolderPivotTable""(#""Sample File"")))," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Expanded Table Column1"",{{""Source.Name"", type text}, {""FAIN"", type any}, {""Fund"", type text}, {""Scope"", type text}, {""ALI"", type text}, {""Project"", type text}, {""Activity"", type text}, {""Resource ID"", " & _
        "type text}, {""Accounting"", type date}, {""Transaction"", type date}, {""System Source"", type text}, {""Journal"", type text}, {""Jrnl Date"", type date}, {""Jrnl Seq"", Int64.Type}, {""Jrnl Ln#"", Int64.Type}, {""Account"", Int64.Type}, {""Employee"", type any}, {""Jobcode"", type any}, {""Department"", Int64.Type}, {""TRC"", type any}, {""Voucher"", type any}, {" & _
        """Vendor"", type number}, {""Vendor Name"", type text}, {""Invoice"", type any}, {""Invoice Date"", type any}, {""Vhcr Ln#"", Int64.Type}, {""Vchr DLn#"", Int64.Type}, {""PO Contract"", type text}, {""PO#"", type text},{""Labor Hours"", type text}, {""ACT Amount"", type number}, {""FRG Amount"", type number}, {""BRD Amount"", type number},{""Total Amount"", type number}, {""RMB Amount"", type number}, {""UTL Amount"", type nu" & _
        "mber}, {""Type"", type text}, {""Package"", type text}})," & Chr(13) & "" & Chr(10) & _
        "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""Source.Name""})" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Removed Columns"""
        
    ActiveWorkbook.Queries.Add Name:="Sample File", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Folder.Files(""" & strPath & """)," & Chr(13) & "" & Chr(10) & _
        "    #""Filtered Rows"" = Table.SelectRows(Source, each ([Name] = """ & StrFileName & """))," & Chr(13) & "" & Chr(10) & _
        "    Navigation1 = #""Filtered Rows""{0}[Content]" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    Navigation1"
        
    ActiveWorkbook.Queries.Add Name:="Sample File Parameter1", Formula:= _
        "#""Sample File"" meta [IsParameterQuery=true, BinaryIdentifier=#""Sample File"", Type=""Binary"", IsParameterQueryRequired=true]"
    
    ActiveWorkbook.Queries.Add Name:= _
        "Transform Sample File from CSVFolderPivotTable", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Csv.Document(#""Sample File Parameter1"",[Delimiter="","", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"""
        
    ActiveWorkbook.Queries.Add Name:="Transform File from CSVFolderPivotTable", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = (#""Sample File Parameter1"") => let" & Chr(13) & "" & Chr(10) & _
        "        Source = Csv.Document(#""Sample File Parameter1"",[Delimiter="","", Columns=35, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
        "        #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & _
        "    in" & Chr(13) & "" & Chr(10) & _
        "        #""Promoted Headers""" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    Source"
    
    
    Workbooks("CSVFolderRefresh.xlsm").Connections.Add2 _
        "Query - CSVFolderPivotTable", _
        "Connection to the 'CSVFolderPivotTable' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=CSVFolderPivotTable;Extended Properties=" _
        , """CSVFolderPivotTable""", 6, True, False
    
    Workbooks("CSVFolderRefresh.xlsm").Connections.Add2 "Query - Sample File", _
        "Connection to the 'Sample File' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sample File"";Extended Properties=""""" _
        , "SELECT * FROM [Sample File]", 2
    
    Workbooks("CSVFolderRefresh.xlsm").Connections.Add2 _
        "Query - Sample File Parameter1", _
        "Connection to the 'Sample File Parameter1' query in the workbook.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Sample File Parameter1"";Extended Properties=""""" _
        , "SELECT * FROM [Sample File Parameter1]", 2
    
    Workbooks("CSVFolderRefresh.xlsm").Connections.Add2 _
        "Query - Transform Sample File from CSVFolderPivotTable", _
        "Connection to the 'Transform Sample File from CSVFolderPivotTable' query in the workbook." _
        , Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform Sample File from CSVFolderPivotTable"";Extended Prop" _
        , "erties="""""), _
        "SELECT * FROM [Transform Sample File from CSVFolderPivotTable]", 2
    
    Workbooks("CSVFolderRefresh.xlsm").Connections.Add2 _
        "Query - Transform File from CSVFolderPivotTable", _
        "Connection to the 'Transform File from CSVFolderPivotTable' query in the workbook." _
        , Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transform File from CSVFolderPivotTable"";Extended Properties=" _
        , """"""), "SELECT * FROM [Transform File from CSVFolderPivotTable]", 2

 

[zoziano]

How do you do this when each workbooks has multiple sheets? For example, I have 10 workbooks, all have a "summary" sheet named "2016", is there a way to tell PowerQuery to only grab the data from the "2016" sheets?