Discussion :

[coyote90]

Bonjour à tous,

Depuis qq jours une question me taraude.
J'utilise PowerQuery pour importer des données dans un classeur Excel.
Je choisi l'option d'importer dans le modèle uniquement. (ce qui permet de dépasser largement les limites sur le nombre de lignes autorisées dans une feuille excel)
Ensuite je créer des tableaux croisés dynamiques et des graphiques de tcd à partir des données du modèle.

L'import des données peut être long, très long.
Lorsque j'utilise "Actualiser tout", il semble que je recharge systématiquement les données depuis les fichiers externes et qu'ensuite les tcd et graphs se mettent à jour.
Le lien vers ces fichiers source doit donc toujours exister.

Hors il m'arrive de devoir partager ce classeur avec des personnes qui n'ont pas accès aux dossiers contenant les fichiers source.

Quelle est votre règle de bonne pratique pour ne pas recharger systématiquement les sources et donc ne pas avoir des message d'erreurs de source inconnu, voire pire, une recherche en boucle très longue avant d'avoir un message d'erreur.

Ma première idées est de modifier le paramètre de ces requêtes PowerQuery pour ne plus les inclure dans "Actualiser tout", et de faire cette actualisation uniquement via vba.
Est-ce la meilleure méthode, j'en doute, surtout avec le déploiement d'excel 365 utilisable en mode web ?

merci pour vos avis.

[Philippe Tulliez]

Bonjour,

Je choisi l'option d'importer dans le modèle uniquement. (ce qui permet de dépasser largement les limites sur le nombre de lignes autorisées dans une feuille excel)

En sélectionnant "Uniquement en connexion" cela permet également de dépasser les limites du nombre de lignes d'une feuille d'excel
Sélectionner le modèle de données permet de travailler sur plusieurs tables et/ou de créer des mesures dans Power Pivot pour les utiliser dans les tcd

Lorsque j'utilise "Actualiser tout", il semble que je recharge systématiquement les données depuis les fichiers externes et qu'ensuite les tcd et graphs se mettent à jour.
Le lien vers ces fichiers source doit donc toujours exister.

C'est le but mais on peut parfaitement utiliser les données dans actualiser celles-ci. On travaille alors sur les données de la précédente actualisation.

Ma première idées est de modifier le paramètre de ces requêtes PowerQuery pour ne plus les inclure dans "Actualiser tout", et de faire cette actualisation uniquement via vba.

On peut parfaitement exclure certaines requêtes de l'actualisation lorsque l'on Actualise tout. Il n'est pas obligqtoire d'utiliser le VBA pour cela

Voir la boîte de dialogue Propriétés de la requête

[coyote90]

Merci Philippe

J'utilise aussi régulièrement le paramètre "Ne créer que la connexion" pour les requêtes dont je n'ai pas besoin dans le modèle.

J'ai essayé de décocher l'option "Actualiser tout" pour toutes les requêtes et fonctions qui cherchent dans dans des fichiers externes.

Mais le jour où je veux actualiser ces liens externes, je ne vois pas d'autre solution que de lancer un "Refresh" requête par requête en vba si je veux éviter un clic bouton droit et actualiser sur chaque requête.

[Promethee25]

Bonjour,

Tu peux aussi créer des "groupes" dans Power Query: ce sont des dossiers permettant d'organiser tes différentes requêtes.

Typiquement, je crée souvent un groupe pour les paramètres, un pour les requêtes qui sont en connexion uniquement et un pour les requêtes sorties dans une table et/ou le modèle de données.
Dans ce dernier, je crée généralement un sous-groupe pour les requêtes que j'appelle "statiques" (celles que je ne rafraîchis pas à chaque "Actualisez tout") et un pour les requêtes "dynamiques" (celles rafraîchies par "Actualisez tout").

Si dans ce sous-groupe "dynamique", j'ai besoin de raffiner pour diverses raisons (ordre de rafraîchissement, volonté de ne rafraîchir qu'une partie comme dans ton cas), je recrée un niveau supplémentaire de sous-groupes.
L'avantage est que tu peux faire un clic droit sur un groupe ou sous-groupe pour actualiser toutes les requêtes qu'il contient (dans le volet Excel des requêtes et connexions).

Malheureusement les groupes ne semblent pas être accessibles en VBA. Passe peut-être par une règle de nommage dans le nom de tes requêtes si tu préfères utiliser du VBA.

[Raccourcix]

...ce qui permet de dépasser largement les limites sur le nombre de lignes autorisées dans une feuille excel)...

Hors il m'arrive de devoir partager ce classeur avec des personnes qui n'ont pas accès aux dossiers contenant les fichiers source.
...
Est-ce la meilleure méthode, j'en doute, surtout avec le déploiement d'excel 365 utilisable en mode web ?
....

Bonjour

Je trouve que le modèle de données Excel est limité lorsque l'on dépasse "largement" le million de lignes. Les données étant stockées à l'intérieur du fichier afin que les utilisateurs n'ayant pas accès aux données source puissent "jouer" avec les données.

Je préfère charger les données dans Power BI et mettre à disposition le modèle sémantique dans un TCD Excel avec des segments, des graphes... pour faciliter les analyses (je ne diffuse pas nécessairement de rapport PBI en tant que tel)

l'intérêt également est qu'on peut gérer la sécurité au niveau des lignes afin d'adapter les données visibles par les utilisateurs suivant leur périmètre respectif.
et qu'on peut gérer le détail des données sous-jacentes (lors du double-clic dans un TCD) - Il faut utiliser Tabular Editor en construisant une table dans le "detail rows expression" de la mesure.

Par contre, avec Excel 365 en mode web vous avez la possibilité de modifier les TCD issus de Power BI mais pas de double-cliquer pour récupérer les données sous-jacentes (il faut ouvrir le fichier en mode bureau)

Stéphane