Microsoft 365 Obtenir le Détail d'un # compte

[Fiat3sg@hotmail.com]

Comment puis-je obtenir le Détail d'un # compte avec toutes les transactions de l'année ou d'une période déterminée?

Dans la base de données onglet "Exercice" le # compte peut se retrouver dans plusieurs colonnes CompteGL1, CompteGL2,..., CompteGL6.

La sélection du compte doit se faire à partir de la liste de comptes dans l'onglet "Balance de vérification" A10:A80.

Le montant à retourner se trouve dans les champs Montant1, Montant2,..., Montant6.

De plus un # compte peut se retrouver 2 fois sur la même ligne puisque attribuer à des projets différents.

Exemple :

Détail du compte :	5510 Conception des outils web
Date	Nom	Description	Montant
2019-01-02	Namecheap	Renouvellement domaine Missisquoinord.com	18,54 $
2019-01-04	Envato Market	Extension adresse autocomplete	-
2019-02-05	Lucie Hébert - Consultante	Premier versement - mandat AMO	1 269,43
2019-02-20	Renée Donaldson	Traduction Circuit	-
2019-03-04	Cha-Cha Communications	Site web et cartes - 1er versement	1 612,31
2019-04-08	Ondago	Facture #1014	201,22
…			…
Total			X XXX,XX

Peut-on le faire avec un TCD (Tableau croisée dynamique)?

 

[Staple1600]

Bonsoir le fil, Fiat

Fiat
Il faut commencer par le point 3)

 

[Hasco]

Bonjour,
Hello @Staple1600

Dans le fichier joint, une solution par requête PowerQuery sur la feuille 'PowerQuery'.
Choix du compte sur liste de validation en D1
L'actualisation de la requête est faite par macro dans le code de la feuille.

Cordialement

[Edition] nouvelle version du fichier au 18/10/2019 11:20

 

[Fiat3sg@hotmail.com]

Bonjour,
Hello @Staple1600

Dans le fichier joint, une solution par requête PowerQuery sur la feuille 'PowerQuery'.
Choix du compte sur liste de validation en D1
L'actualisation de la requête est faite par macro dans le code de la feuille.

Cordialement

[Edition] nouvelle version du fichier au 18/10/2019 11:20

Merci beaucoup pour votre aide. Cela fonctionne très bien.
J'aimerais en connaître le fonctionnement. Pourriez-vous m'expliquer comment vous l'avez bâti ou me référer un tutoriel de formation?

 

[Hasco]

Re,

Nous n'avons pas les mêmes versions d'excel et j'ai bien peur que powerquery ait évolué depuis la version addin 2010. Quoiqu'il en soit, la démarche générale et de décrire à travers différentes étapes, comment manipuler les données. Je ne connais pas de Tuto sur le sujet, même pour votre exemple j'ai du me débrouiller avec la doc miséreuse de MS https://docs.microsoft.com/fr-fr/powerquery-m/index (le chapître sur les fonctions M).

En sachant que la requête nommé 'prmCompteRecherché' ne fait que retourner la valeur de la cellule nommée 'Compte.Recherché' (D1),
je vais vous décrire les étapes de la requête 'DétailsCompte':

1 - la source : Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content] Ramène toutes les données du tableau "Tableau1"

2 - établir une liste des noms de colonnes des 'CompteG' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"CompteG"))

3 - établir une liste des colonnes 'Montant?' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Montant"))

4 - sélectionner les colonnes du tableau 'Tri' à 'Description' plus celles des deux listes précédentes : Table.SelectColumns(Source,List.Combine({{"Tri", "Date", "Nom", "Description"},lstColsComptes,lstColsMontants}))

5 - sélectionner les lignes dont au moins une colonne est égale au paramètre 'prmCompteRecherché' : = Table.SelectRows(SelectionColonnes, each List.Contains(Record.FieldValues(Record.SelectFields(_,lstColsComptes)),prmCompteCherché))

6 - ajouter une colonnes 'Montants' chargée de sommer les valeurs des colonnes idoines. Table.AddColumn(SelectionLignes, "Montants", each fnSommer(_))

Pour cette étape j'ai créé à l'intérieur de la requête une fonction nommer 'fnSommer' qui reçoit en paramètre une ligne d'enregistrement.
La fonction transforme la ligne d'enregistrement en Table, établit une liste des noms de CompteG qui ont pour valeur le compte recherché.
A partir de cette liste, par remplacement de 'CompteGL' par 'Montant' est établit une liste des lignes à sommer.

Cordialement

 

[Fiat3sg@hotmail.com]

Merci !

 

[Staple1600]

Bonsoir le fil, Fiat, Roblochon

Fiat
Pour info: le point 3) (évoqué dans le message#2) reste d'actualité...
Pour être plus limpide dans mon propos, voir ci-dessous
extrait de la charte du forum

3 – Lorsqu’on rentre sur un fil, comme dans la vie de tous les jours, on est poli en disant « Bonjour ».

 

[Hasco]

Re,
Hi Staple
J'avions même point vu!!! bougre! j'aurions vu j'aurions point répondu! plutôt râler
J'avions point compris ceci non plus:

Il faut commencer par le point 3)

Bonne soirée l'ami

 

[Staple1600]

Re

Roblochon
C'est pas grave au moins la lecture de ce fil m'aura au travers de tes réponses obliger à me tourner de plus en plus vers PowerQuery.
Puisque tu parles d'avions
Avions-tu point remarqué que le pseudo du demandeur est un email...?
J'attends le petit Bonjour qui va bien pour me fendre de l'amical conseil relatif au spam...
Ou je me focalise vers la Puissante Requête ?

 

[Hasco]

Re,

Ou je me focalise vers la Puissante Requête ?

...arf, les deux mon capitaine !

Chalut

 

[Fiat3sg@hotmail.com]

Re,

Nous n'avons pas les mêmes versions d'excel et j'ai bien peur que powerquery ait évolué depuis la version addin 2010. Quoiqu'il en soit, la démarche générale et de décrire à travers différentes étapes, comment manipuler les données. Je ne connais pas de Tuto sur le sujet, même pour votre exemple j'ai du me débrouiller avec la doc miséreuse de MS https://docs.microsoft.com/fr-fr/powerquery-m/index (le chapître sur les fonctions M).

En sachant que la requête nommé 'prmCompteRecherché' ne fait que retourner la valeur de la cellule nommée 'Compte.Recherché' (D1),
je vais vous décrire les étapes de la requête 'DétailsCompte':

1 - la source : Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content] Ramène toutes les données du tableau "Tableau1"

2 - établir une liste des noms de colonnes des 'CompteG' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"CompteG"))

3 - établir une liste des colonnes 'Montant?' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Montant"))

4 - sélectionner les colonnes du tableau 'Tri' à 'Description' plus celles des deux listes précédentes : Table.SelectColumns(Source,List.Combine({{"Tri", "Date", "Nom", "Description"},lstColsComptes,lstColsMontants}))

5 - sélectionner les lignes dont au moins une colonne est égale au paramètre 'prmCompteRecherché' : = Table.SelectRows(SelectionColonnes, each List.Contains(Record.FieldValues(Record.SelectFields(_,lstColsComptes)),prmCompteCherché))

6 - ajouter une colonnes 'Montants' chargée de sommer les valeurs des colonnes idoines. Table.AddColumn(SelectionLignes, "Montants", each fnSommer(_))

Pour cette étape j'ai créé à l'intérieur de la requête une fonction nommer 'fnSommer' qui reçoit en paramètre une ligne d'enregistrement.
La fonction transforme la ligne d'enregistrement en Table, établit une liste des noms de CompteG qui ont pour valeur le compte recherché.
A partir de cette liste, par remplacement de 'CompteGL' par 'Montant' est établit une liste des lignes à sommer.

Cordialement

Désolé mais je ne comprends pas comment le refaire moi-même. J'ai des bases en PowerQuery et PowerPivot mais ce ne semble pas suffisant. Faut-il créer ces lignes de macro ou cela se fait par une requête?

 

[Hasco]

Bonjour,

Où voyez-vous des lignes de macro? Ce n'est que la description de la requête que je vous ai faite.
Je ne sais pas quoi vous dire en plus.
Pour interpréter les lignes de requête, vous pouvez vous aider de l'aide MS: M Formules

Eventuellement, sur un point précis revenez avec un fichier exemple contenant votre requête.

Bonne journée

 

[Fiat3sg@hotmail.com]

OK mais comment exécuter ces requêtes avec PowerQuery? Pourriez-vous me décrire les étapes? ex. :

Comment réaliser chacune de ces étapes par ex. pour "Lignes filtrées" : va t-on dans 1- Éditeur Power Query 2- Accueil / Trier / ... OU Transformer / ...

Lequel permet d'obtenir cette fonction : = Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché) or ([CompteGL2] = prmCompteCherché) or ([CompteGL3] = prmCompteCherché) or ([CompteGL4] = prmCompteCherché) or ([CompteGL5] = prmCompteCherché) or ([CompteGL6] = prmCompteCherché))


Ex. 2

Lequel donne :

De plus, comment cela apparaît-il?

J'espère que ce n'est pas trop vous demander?

J'apprécie beaucoup votre aide.

 

[Hasco]

Re,

Ben c'est à dire que vous faire une formation sur PQ sort totalement du cadre du forum.
Pour le filtre, Sélectionnez l'étape 'Colonnes supprimées'
A droite de l'étiquette de la colonne 'CompteGL1' vous avez un icône de filtre, cliquer et sélectionnez un item:

Vous valider et vous obtenez l'étape 'Lignes filtrées' avec dans la barre de formule:
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = "5510 Conception des outils web"))
Dans la barre de formule, vous remplacez "5510 Conception des outils web" (guillemets compris) = prmCompteCherché (sans guillemet)
ce qui vous donnera:
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché))
Vous copiez ([CompteGL1] = prmCompteCherché)
1 - Vous tapez une espace et 'or' et une espace après la parenthèse fermante de prmCompteCherché)
2 - Puis vous coller (CTRL+V) Pour Obtenir ça:
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché) or ([CompteGL1] = prmCompteCherché))
3 - Vous changez le deuxième 'CompteGL1' par 'CompteGL2' et vous recommencez de 1 à 3 jusqu'à obtenir
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché) or ([CompteGL1] = prmCompteCherché) or ([CompteGL2] = prmCompteCherché) or ([CompteGL3] = prmCompteCherché) or ([CompteGL4] = prmCompteCherché) or ([CompteGL5] = prmCompteCherché))
et valider
Faites attention aux parenthèses.

Dans le post 5 je vous ai donné le lien vers la doc du langage M. servez-vous en.

Bon apprentissage

 

[jcf6464]

Bonjour à tous et le forum

il na toujours pas ( bonjour)

bonne soirée à tous jcf