Power Query in Excel 2016

Copper Contributor
Hi to eveyone,
I would like to use :
Dim
request$ request$ = InputBox("Fully pathed query source...", "Init query...", "")   ActiveWorkbook.Queries.Add Name:="Banque19", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents( request$ , null, true)," & Chr(13) & "" & Chr(10) & " Banque19_Sheet = Source{[Item=""Banque19"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Banque19_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type text}, {""Column5"", type any}, {""C" & _ "olumn6"", type number}, {""Column7"", type any}, {""Column8"", type text}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type text}" & _ ", {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type number}})," & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"""

so as to be able to input the request, instead of
 ActiveWorkbook.Queries.Add Name:="Banque19", Formula:= _
      "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\MyMenu\Compta\CtaHH2019.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & "    Banque19_Sheet = Source{[Item=""Banque19"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(Banque19_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type text}, {""Column5"", type any}, {""C" & _
      "olumn6"", type number}, {""Column7"", type any}, {""Column8"", type text}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type text}" & _
      ", {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Autres colonnes supprimées"""
 
which requires a preset "request$"

Thanks for your suggestions,
Hervé
 
 
1 Reply

Power Query cannot throw up a dialog box asking for input like VBA can, but you can still pass variables to it. There are several ways to do it, but this is the easiest:

  1. Create a table that is one record. Each column is a variable you want. Things like Start Date, End Date, Customer Number, etc. Whatever. One column for each variable.
  2. Enter the data in the first record of the table.
  3. Load the table into Power Query. Change the types to be accurate, but do nothing else.
  4. For the first variable, right-click the Excel table query in step 3 and select Reference.
  5. Remove all columns except for the first field.
  6. Right-click on that value, and select Drill Down.
  7. Name the query something useful, and preferably without spaces. I use pm for parameter, so pmStartDate, pmEndDate, pmCustomerNumber, etc.
  8. Repeat steps 4-7 for all fields in the table.
  9. Now you can reference those variables in your queries. For example, if you filter a query by date, just pick a bogus date. it will show something like = #date(2019,1,1) in the filter. Replace "#date(2019,1,1)" in the formula bar with pmStartDate.

 

Now when values are changed in the Excel table, your queries will use that data accordingly. You can use these in filters, mathematical functions, etc. Anywhere you would want to use a variable.