Feb 14 2019
08:10 AM
- last edited on
Jul 12 2019
11:20 AM
by
TechCommunityAP
Feb 14 2019
08:10 AM
- last edited on
Jul 12 2019
11:20 AM
by
TechCommunityAP
Hi to eveyone, 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""" |
Feb 17 2019 05:53 AM
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:
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.