Effacer les valeurs dans une cellule en doublon sans supprimer la ligne

Le 27/04/2022 à 22:15

Bonsoir JFL,

Je reviens après une période de déconnection.

Je voudrais comprendre les opérations.
Quand je me mets sur l'onglet résultats de power query et que je fais click droit et table / modifier requête il apparait un petit tableau gris.

C'est quand je vais sur requête dans outils de requête qu'apparaît un tableau 1 éditeur de PQ avec à droite les étapes appliquées.

Quand je parle de commande des opérations je parle des fonctions ou des sommes etc que l'on applique.

Je ne sais pas le dire autrement.

Je voudrais par exemple renommer les colonnes ... etc en ajouter ...

Le 28/04/2022 à 13:12

Bonjour à tous,

N'ayant pas Excel 2016, je ne suis pas en mesure de vous donner précisément la démarche pour accéder à l'éditeur Power Query. Mais il semble que vous y arrivez via le ruban. Tant mieux !

Je voudrais par exemple renommer les colonnes ... etc en ajouter ...

C'est possible....mais il conviendra de passer en revue les différentes étapes de la requête pour mesurer l'impact de ces changements ! C'est pourquoi nous répétons continuellement de nous présenter un classeur représentatif de votre réalité.......

Quand je parle de commande des opérations je parle des fonctions ou des sommes etc que l'on applique

Je vous propose une "bible" : ici !

Le 10/06/2023 à 20:39

Bonjour à tous,

Je voudrais reprendre le sujet.

Je me suis mis sur Power Query. A l'époque JFL m'avait aidé avec une procédure. Cela fonctionnait très bien.

J'essaie donc de décortiquer les différentes étapes du dernier fichier adapté car je devrai adapter la procédure.

J'ai naturellement regardé dans la bible Power Query ... mais je bloque.

De plus, je voudrais être autonome en la matière et je dois avoir le même type de résultat mais donc avec une contrainte supplémentaire.

Donc,

- quand j'ouvre les requêtes, je vois ... mais je ne vois pas à quoi cela correspond ? Pourrais-tu me dire comment tu as procédé ?

10mise-en-page-pq-01.xlsx (24.54 Ko)

Buffer = Table.Buffer( Source),
GroupBy_MatriculeDate = Table.Group(Buffer, {"Matr.", "Date"}, { {"tbl", each Table.AddIndexColumn(_,"Idx1",1)}}),
tbl_dével = Table.ExpandTableColumn(GroupBy_MatriculeDate, "tbl", {"Année - Mois", "Année - Trimestre", "Co9age", "Done", "Idx1", "Index", "NOM", "Rubriques", "TSD", "Types Absences"}, {"Année - Mois", "Année - Trimestre", "Co9age", "Done", "Idx1", "Index", "NOM", "Rubriques", "TSD", "Types Absences"}),
Ajt_TSD_Regul = Table.AddColumn(tbl_dével, "TSD_Regul", each if[Idx1]=1 then [TSD] else 0),

etc....

Je joins un autre fichier.

En fait, je devrais arriver au même résultat mais avec une contrainte supplémentaire à savoir ne garder qu'une valeur TSD par jour sauf si la personne est renseignée ce jour la dans un autre service. Donc, même service ou doit avoir 1 valeur dans TSD sauf si pour le même jour il y a deux services différents.

Je voudrais pouvoir recréer la première procédure de manière à arriver à la contrainte supplémentaire.

D'avance merci.

Toonies

Le 10/06/2023 à 23:12

Bonsoir à tous !

Une proposition ? (Compte tenu de ma compréhension de cette nouvelle contrainte.....) :

11tooniesgamer-pq-mise-en-page-pq-01-v1.xlsx (53.93 Ko)
Le 11/06/2023 à 10:27

Bonjour,

Et bon dimanche

J'ai testé sur un échantillon et cela fonctionne. Je vais tester à grande échelle.

* Pour rappel et pour être clair : la demande était la suivante : j'ai un calendrier avec des prestations par jour par personne. Parfois, il y a deux prestations pour un même jour pour ce même travailleur. Il y a un doublon mais je ne peux supprimer la ligne mais bien effacer le doublon dans la colonne prestation ... sauf, et c'est nouveau, si la personne se t-retrouve dans deux services. Dans ce cas, on doit garder le doublon.

* Par contre, je pense que je devrai dans l'avenir devoir adapter le fichier et donc ... refaire une procédure Power Query. Comme chaque fois, j'essaie de comprendre.

Est-ce que tu pourrais même en PM me décrire plus précisément en langage simple les opérations ?

Je vois bien les opérations mais je ne comprends pas vraiment. Je pense que si tu me décis je pourrai la reproduire.

C'est donc ... encore une fois excellent.

Toonies

Le 11/06/2023 à 10:53

Bonjour à tous !

Code de la requête documenté :

let
    // Chargement de la source.  Utilisation de la fonction Table.Buffer pour accélérer l'exécution de la requête (sans garantie !)
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="t_Source"]}[Content]),

    // Regroupement de la table sur la base du Numéro et de la date pour :
    //   - Insérer un index
    //   - déterminer le nombre de services uniques concernés (Si =1 alors pour une jounrée donnée et un néméro donné, le/les services(s) est/sont identique(s) )
    GroupBy = Table.Group(Source, {"Numéro","Date"}, {{"tbl", each Table.AddIndexColumn(_,"Idx0",0)}, {"NbreServices", each List.Count(List.Distinct(_[Service]))}}),

    // Extension de la table tbl. Le choix des champs est dynamique. L'insertion d'un champ nouveau est protégée.
    Expand = Table.ExpandTableColumn(GroupBy, "tbl", List.Select(Table.ColumnNames( GroupBy[tbl]{0}), each _ <>"Numéro" and _ <>"Date")),

    // Remplacement éventuel du champ "TSD"
    ValRempl = Table.ReplaceValue(Expand,each [TSD],each if [NbreServices]=1 and [Idx0]<>0 then 0 else [TSD],Replacer.ReplaceValue,{"TSD"}),

    // Suppression des champs inutiles
    ColSupp = Table.RemoveColumns(ValRempl,{"Idx0", "NbreServices"})
in
    ColSupp

Par ailleurs, certains champs pourraient être calculés directement dans Power Query :
- Différence
- Ratio
- Année - Mois
- Année - Trimestre

Le 11/06/2023 à 11:15

bonjour AuSecours, JFL, Tooniesgamer,

une autre proposition qui utilise temporairement la colonne AA (à modifier si nécessaire)

Sub Avec_Formule()
     t = Timer
     Application.ScreenUpdating = False
     Set c = Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)     'vos données en colonne I
     Set c1 = c.Offset(, 27 - c.Column)      'plage auxiliaire en colonne AA, même nombre de lignes que I
     c1.FormulaR1C1 = "=IF(RC3=R[-1]C3,0,RC9)"     'formule
     c.Value = c1.Value         'coller les résultats
     c1.ClearContents                        'supprimer colonne AA
     MsgBox "prêt en " & Format(Timer - t, "0.00\s")
End Sub
Le 14/06/2023 à 18:58

Bonsoir Ausecours, JFL, BsAlv,

Je n'ai pu répondre plus vite.

Je tenais à vos remercier pour votre aide qui m'est très précieuse.

Les propositions de solutions sont parfaites.

Je voudrais quand même pouvoir approfondir Power Query.

J'essaie de reproduire la mise ne forme à partir des données mais je n'y arrive pas.

Je vois les commandes mais je ne vois pas comment les appliquer en pratique.

Donc, si je commence par le début. Je ne vois pas comment appliquer en pratique cette commande ?

Est-ce que je dois écrire quelque chose ou est ce que cette commande se trouve dans les menus ?

Désolé pour les questions mais ... j'essaie d'apprendre

Cordialement.

TG

Le 14/06/2023 à 19:13

Bonsoir à tous !

Difficile de vous accompagner sans un minimum d'auto-formation !

Il existe sur le net une foultitude de tutoriels vidéos qui vous permettront, à votre rythme, d'acquérir les premières bases.

Vous pourrez ensuite revenir vers la communauté pour d'éventuels points à préciser.

Osez.......vous vous rendrez les choses plus faciles.

Bon courage et..... bonnes découvertes.

Le 26/06/2023 à 00:11

Bonjour JFL,

Je progresse dans powerquery.

Pour revenir à cette procédure créée ici.

J'aurais deux questions.

Est-ce que je dois d'abord regroupé ? Ou dois je créer la colonne index ? On parle le bien de regrouper on parle bien de regrouper dans l'onglet transformation ?

Quand je crée la colonne index j'ai une incrémentation qui se fait. Pas une colonne avec 0 ou 1.

Le 26/06/2023 à 09:24

Bonjour à tous !

L'étape de regroupement (via l'onglet Transformation ou l'onglet Accueil) de ma requête (voir les commentaires du code) permet :

  1. de créer automatiquement l'index ---> {"tbl", each Table.AddIndexColumn(_,"Idx0",0)}
  2. de dénombrer le nombre de services.

Ces deux éléments vont permettre, dans l'étape ValRempl, la mise à jour des données.

Si l'ajout direct de l'index vous pose souci, voici un exemple de requête un peu moins condensée :

let
    // Chargement de la source.  Utilisation de la fonction Table.Buffer pour accélérer l'exécution de la requête (sans garantie !)
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="t_Source"]}[Content]),

    // Regroupement pour :
    // 1/ dénombrer les services
    // 2/ créer une table par regroupement.
    GroupBy = Table.Group(Source, {"Numéro", "Date"}, {{"NbreServices", each Table.RowCount(_), Int64.Type}, {"tbl", each _, type table [Numéro=number, NOM=text, Date=datetime, #"Année - Mois"=text, #"Année - Trimestre"=text, Main Code=any, Type Absence=text, Rubrique=any, TSD=number, Prestation=number, X=text, Différence=number, Ratio=number, S=text, Service=text]}}),

    // Ajout d'un index dans chacune des tables.
    Ajt_Index = Table.AddColumn(GroupBy, "Personnalisé", each Table.AddIndexColumn([tbl],"Idx0",0)),

    // Suppression du champ congtenant les tables sans index
    ColSupp0 = Table.RemoveColumns(Ajt_Index,{"tbl"}),

    // Développement de la table contenant l'index
    Expand = Table.ExpandTableColumn(ColSupp0, "Personnalisé", {"NOM", "Année - Mois", "Année - Trimestre", "Main Code", "Type Absence", "Rubrique", "TSD", "Prestation", "X", "Différence", "Ratio", "S", "Service", "Idx0"}, {"NOM", "Année - Mois", "Année - Trimestre", "Main Code", "Type Absence", "Rubrique", "TSD", "Prestation", "X", "Différence", "Ratio", "S", "Service", "Idx0"}),

    // Remplacement éventuel du champ "TSD"
    ValRempl = Table.ReplaceValue(Expand,each [TSD],each if [NbreServices]=1 and [Idx0]<>0 then 0 else [TSD],Replacer.ReplaceValue,{"TSD"}),

    // Suppression des champs inutiles
    ColSupp1 = Table.RemoveColumns(ValRempl,{"Idx0", "NbreServices"})
in
    ColSupp1
Le 27/06/2023 à 17:53

Bonjour JFL,

Bon... je n'y arrive vraiment pas. Mais je ne suis pas certain de bien comprendre quand tu dis regrouper numéro et service.

En fait quand je vais dans accueil / regrouper par, je sélectionne "numéro et service" dans le tableau de powerquery.

Ensuite s'ouvre une petit écran avec de base et avancé. Je coince ici parce que je sélectionne avance et je prendre dans le menu numéro et date et compter les lignes. Mais je ne sais rien faire de plus. J'ajoute une colonne index et je mets sur 1 mais je n'ai pas le resultat dont tu me parles

Désolé mais je ne vois pas

Le 27/06/2023 à 18:05

Bonsoir à tous !

Avez-vous essayé avec la requête moins condensée ?

Le 28/06/2023 à 17:19

Bonjour,

Je crois que j’ai tout essayé. 😀

Mais j’avance.

Mon but est de pouvoir refaire toute la procédure depuis le début en utilisant les points de menu.

J’ai un peu triché en copiant carrément les lignes de code de la procédure dans celle que je veux reconstituer. Ce qui fonctionne.

Mais la je coince avec expand. Quelle est la commande dans le menu qui permet cette opération ?

Je pense que si je passe cette étape je devrais y arriver

Le 28/06/2023 à 17:23

Bonjour,

Je crois que j’ai tout essayé. 😀

Mais j’avance.

Mon but est de pouvoir refaire toute la procédure depuis le début en utilisant les points de menu.

J’ai un peu triché en copiant carrément les lignes de code de la procédure dans celle que je veux reconstituer. Ce qui fonctionne. J’arrive à compter les services.

Mais la je coince replace value avec expand. Quelle est la commande dans le menu qui permet cette opération ?
Si je veux copier il me dit expand n’est pas reconnu !

Je pense que si je passe cette étape je devrais y arriver

Le 28/06/2023 à 17:32

Bonjour à tous !

tooniesgamer a écrit :

Mais la je coince avec expand. Quelle est la commande dans le menu qui permet cette opération ?

Je pense que si je passe cette étape je devrais y arriver

L'étape nomme "Expand" vis à développer un champ. Dans la première requête par exemple, si vous vous placez sur l'étape GroupBy., votre écran devrait se présenter ainsi :

image

En cliquant sur l’icône à droite du champ "tbl", vous accéderez aux différentes colonnes de la table. A vous de faire le choix ! Un code M sera généré en fonction de celui-ci.

Vous avez raison d'être pugnace...

Le 28/06/2023 à 23:14

Ok. Merci. J'ai compris cette étape...

Je dois maintenant remplacer les valeurs.

Je lis ceci : = Table.ReplaceValue(Expand,each [TSD],each if [NbreServices]=1 and [Idx0]<>0 then 0 else [TSD],Replacer.ReplaceValue,{"TSD"})

mais quand je vais dans Remplacer valeur je ne sais pas renseigner dans l'écran qui se crée. Est-ce que l'on parle bien de Remplacer valeur dans le menu Transformer ? Je ne vois pas où renseigner Nombre de service et IDX ???

Le 29/06/2023 à 10:30

Bonjour à tous !

tooniesgamer a écrit :

Je dois maintenant remplacer les valeurs.

Je lis ceci : = Table.ReplaceValue(Expand,each [TSD],each if [NbreServices]=1 and [Idx0]<>0 then 0 else [TSD],Replacer.ReplaceValue,{"TSD"})

mais quand je vais dans Remplacer valeur je ne sais pas renseigner dans l'écran qui se crée. Est-ce que l'on parle bien de Remplacer valeur dans le menu Transformer ? Je ne vois pas où renseigner Nombre de service et IDX ???

Pour produire cette ligne de code, il faut en passer par la barre de formule.

Si vous deviez, en passant par le ruban, remplacer les valeurs 99999 par 88888 du champ TSD, vous obtiendriez le code suivant : Table.ReplaceValue(Expand,99999,88888,Replacer.ReplaceValue,{"TSD"})
Pour faciliter l'élaboration de la ligne finale, vous pouvez créer temporairement une colonne additionnelle et tester l'opération de transformation ( if [NbreServices]=1 and [Idx0]<>0 then 0 else [TSD] )

Ensuite vous modifiez la formule créée via le ruban :
99999 ---> each [TSD] (le traitement doit s'opérer sur chacune des valeurs)
88888 ---> each if [NbreServices]=1 and [Idx0]<>0 then 0 else [TSD]

Le 29/06/2023 à 13:40

Ok. Je commence à comprendre quelque chose que je n’avais pas capté ! Tout ne se fait pas avec le menu. Il faut aussi écrire certaine « formule » dans cette fameuse zone de texte et si je comprends bien en utilisant les commandes renseignées dans la bible Power Query. Je pensais que l’on pouvait les retrouver dans les menus. Je comprends pourquoi je ne les voyais pas !😀

Pour que l’on parle bien de la même choses

Ce que vous appelez le ruban c’est bien les menus transformer etc … ?

La barre de formule c’est bien ou se retrouve ce texte en format programmation ?

Je pense qu’ici le mieux est que je copie la ligne

Je vais voir cela ce soir.

Le 29/06/2023 à 16:09

Bonjour à tous de nouveau !

Oui, ce que l'on nomme "ruban" correspond à l'ensemble des onglets (5) vous permettant d'accéder aux commandes en cliquant.

La barre de formule est cette zone :

image
Rechercher des sujets similaires à "effacer valeurs doublon supprimer ligne"