Discussion :

[pokypok]

Bonjour cette discussion est à cheval entre Excel et SQL Serveur désolé ...
Je me connecte à ma base SQL j'arrive à importer une table tout est ok.

Prenons par exemple la banale recherche de factures émises entre deux dates deux exercices.
Ce que j'aimerai c'est d'avoir deux cellules A2 et A3 dans lesquelles l'utilisateur choisis deux dates et que la requête aille chercher les résultats bornés par ces deux dates.

Suis-je obligé de passer par une ou des macros?

[Pierre Dumas]

Bonsoir

Si vous avez effectué une requête Power Query pour aller chercher les données dans SQL Server, vous pouvez passer en paramètres les valeurs des cellules A2 et A3.

Pour cela nommer chacune de ces deux cellules.

Puis, dans la requête, remplacer chacune des valeurs en dur des dates déjà inscrites par :

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

Excel.CurrentWorkbook(){[Name="NomDeLaCellule"]}[Content]{0}[Column1]

En espérant que cela aide.

Bonne fin de journée

Pierre Dumas

[Promethee25]

Bonjour,

Laisses-tu Power Query gérer le SQL par lui-même ou bien utilises-tu une requête SQL faite "à la main" dans ta requête Power Query?
Dans les deux cas, ce que tu souhaites est possible mais pour les performances, il faut veiller à ce que ce soit la base de données SQL qui applique le filtre de date et non ton PC. Si c'est ton PC qui le fait, cela veut dire que les données seront téléchargées sans le filtre de date sur ton PC et le filtrage sera ensuite fait dans une étape ultérieure, ce qui est beaucoup plus lent.

• Dans le cas où tu laisses Power Query gérer le SQL, cela correspond au "Query Folding" qui est la capacité de Power Query à traduire une partie plus ou moins grande des étapes de la requête Power Query en SQL selon comment elles sont organisées.
• Dans le cas d'une requête SQL paramétrée embarquée dans ton script, je recommande l'utilisation de la fonction Value.NativeQuery qui permet de passer des requêtes ou paramètres Power Query en paramètres SQL.

Il faudrait un peu plus de détails pour affiner.

[pokypok]

Pierre Dumas et Promethee25 merci beaucoup à tous les deux pour vos informations.

@Promethee effectivement le mieux serait de faire faire le tri à ma base de données et de ne récupérer que le résultat.
Si par exemple la requête voulue est de la forme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT *
FROM bdd.dbo.table
WHERE ReceiveDate between '2022-04-01' and '2023-04-01'
ORDER BY ReceiveDate

Afin qu'elle soit exécutée par la base de données comment devrais-je procéder?

@Pierre Dumas si j'ai bien compris nommant mes deux cellules bornes PremiereCellDate et DeuxiemeCellDate cela donnerai:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
WHERE ReceiveDate between Excel.CurrentWorkbook(){[Name="PremiereCellDate "]}[Content]{0}[Column1] and Excel.CurrentWorkbook(){[Name="DeuxiemeCellDate "]}[Content]{0}[Column1]

[Promethee25]

Bonjour,

Pour une requête aussi simple, je laisserais le "query folding" de Power Query générer le SQL lui-même car cela offre plus d'avantages (même s'il a tendance à imbriquer deux SELECT quand on met un tri).

Exemple avec une requête MinDate et une requête MaxDate récupérant les deux paramètres Excel et utilisées par la requête attaquant la BDD:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// MinDate
let
    Source = Date.From(Excel.CurrentWorkbook(){[Name="MinDate"]}[Content]{0}[Column1])
in
    Source
 
// MaxDate
let
    Source = Date.From(Excel.CurrentWorkbook(){[Name="MaxDate"]}[Content]{0}[Column1])
in
    Source
 
// TableSQL
let
    Source = Sql.Database("ServerName", "DatabaseName"),
    RawTable = Source{[Schema="SchemaName",Item="TableOrViewName"]}[Data],
    #"Filtered Rows" = Table.SelectRows(RawTable, each [Date] >= MinDate and [Date] <= MaxDate),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"DT number", "Facility", "Date", "User"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"DT number", Order.Ascending}})
in
    #"Sorted Rows"

Avec cette requête, Power Query génère le SQL suivant (visible via clic droit sur la dernière étape de la requête => "Afficher la requête native"):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select [_].[DT number],
    [_].[Facility],
    [_].[Date],
    [_].[User]
from 
(
    select [_].[DT number],
        [_].[Facility],
        [_].[Date],
        [_].[User]
    from [SchemaName].[TableOrViewName] as [_]
    where [_].[Date] >= convert(datetime2, '2023-02-21 00:00:00') and [_].[Date] <= convert(datetime2, '2023-03-21 00:00:00')
) as [_]
order by [_].[DT number]

NB: Pour que le Query folding couvre le plus d'étapes d'une requête (et de la façon la plus optimale), l'ordre des étapes est à gérer avec soin. Il est même capable de gérer des requêtes fusionnées mais va commencer à générer du SQL très peu performant avec un nombre potentiellement important de SELECT imbriqués (en tout cas avec Excel 2016).
https://learn.microsoft.com/en-us/po...-query-folding

[pokypok]

Waouh quelle réponse ... merci pour le temps passé.
Je vais m'y atteler merci beaucoup.

Bien sûr j'avais mis cette requête très simple en fait j'en ai deux trois avec 6 à 10 jointures ...

Petite question subsidiaire ... lors de la première mise en route en sortant de PowerQuery je fais ajouter le tableau résultant où je veux mais après l'utilisateur n'aura plus qu'à changer les dates et "Actualiser" à partir du ruban c'est bien ça ?

[Promethee25]

Bien sûr j'avais mis cette requête très simple en fait j'en ai deux trois avec 6 à 10 jointures ...

Petite question subsidiaire ... lors de la première mise en route en sortant de PowerQuery je fais ajouter le tableau résultant où je veux mais après l'utilisateur n'aura plus qu'à changer les dates et "Actualiser" à partir du ruban c'est bien ça ?

Bonjour,

C'est précisément ce genre d'infos que j'attendais quand je disais qu'il fallait plus de détails pour affiner.
Le problème des jointures est que ça se configure facilement directement dans Power Query sans SQL (surtout si tu pointes sur des tables et non des vues car il te met directement à disposition dans les dernières colonnes de la table Power Query les tables SQL liées via des clés étrangères) mais que chaque jointure va générer un SELECT imbriqué supplémentaire dans la requête native générée par Power Query, ce qui risque de devenir très lent selon la taille de tes tables.
J'ai déjà constaté du 40s au lieu de 4s en ayant fait le SQL moi-même par exemple.

L'autre solution consiste donc à embarquer directement une requête SQL paramétrée dans le script Power Query.
En version générique:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
let
    Source = Sql.Database("ServerName", "DatabaseName"),
    #"SQL Query" = "
<Contenu de la requête SQL>
    ",
    #"Get Server Data" = Value.NativeQuery(Source, #"SQL Query", [ParamètreSQL1=ValeurAAffecter, ParamètreSQL2=ValeurAAffecter <, etc si plusieurs paramètres>])
in
    #"Get Server Data"

A noter:
Si aucun paramètre dans la requête SQL, il faut retirer le 3ème argument de Value.NativeQuery.
Ce 3ème argument est un enregistrement (type "Record") dans lequel on peut mettre les valeurs à affecter pour tous les paramètres SQL requis, séparés d'une virgule.
Dans cet enregistrement, il ne faut pas reprendre le "@" pour le nom du paramètre SQL (@MinDate devient donc MinDate).
On peut affecter n'importe quelle valeur Power Query (ex: une requête ayant le même nom (ou pas) que le paramètre SQL, ou bien encore le résultat d'une formule ou d'une étape précédente de la requête).
La déclaration et l'affectation des variables SQL doivent être retirées ou commentées lorsqu'on utilise une requête SQL dans un script Power Query sinon il lèvera une erreur (personnellement, je les commente comme ça c'est facile à réutiliser dans SSMS pour évolution et test ultérieurs).

Exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
// MinDate
let
    Source = Date.From(Excel.CurrentWorkbook(){[Name="MinDate"]}[Content]{0}[Column1])
in
    Source
 
// MaxDate
let
    Source = Date.From(Excel.CurrentWorkbook(){[Name="MaxDate"]}[Content]{0}[Column1])
in
    Source
 
// SQL Table
let
    Source = Sql.Database("ServerName", "DatabaseName"),
    #"SQL Query" = "
/* Déclaration et affectation des paramètres SQL qui doivent être commentées quand le script SQL est embarqué dans un script Power Query */
/*
DECLARE @MinDate DATE
DECLARE @MaxDate DATE
SET @MinDate = '2023-02-23'
SET @MaxDate = '2023-03-23'
*/
 
/* SELECT */
SELECT [Id]
      ,[DT Id]
      ,[Revision]
      ,[Date]
      ,[User Id]
      ,[Estimated cost]
      ,[Revision status Id]
      ,[File Id]
  FROM [DatabaseName].[dbo].[T_DT_Revisions]
  /* et autres JOIN... */
  WHERE [Date] BETWEEN @MinDate AND @MaxDate
    ",
    #"Get Server Data" = Value.NativeQuery(Source, #"SQL Query", [MinDate=MinDate, MaxDate=MaxDate])
in
    #"Get Server Data"

[pokypok]

Un énorme merci maintenant me mettre au frai et transposer mes requêtes !!!!!!