Power Query Découper une colonne en plusieurs colonnes

[Lolote83]

Bonjour à tous,

j'ai une base ci-contre composée de code en français (100-FR1, 100-FR9) avec leur correspondance en anglais (100-US1, 100-US9).

J'aurais aimé savoir si avec une requête PowerQuery on pouvait directement arriver au résultat (Voir onglet résultat)

Pour cela, j'ai fait une requête PQ (onglet REQ1) ou je dissocie le champ Code en (nombre, pays et numéro) et je lance ensuite une macro afin d'arriver au résulat final.

Question : Est-il possible de le faire directement avec PowerQuery ?

C'est à dire affecter en face d'un numéro à la fois la partie FR et US.
Exemple : Pour le 100-1 récupérer le 100-FR1 (champ FR) et 100-US1 (Champ US).
J'ai essayé avec une requête PQ en dupliquant la colonne TEXTE, en dépivotant les colonnes etc etc mais je n'y arrive pas. J'utilise PQ que très rarement et j'ai encore beaucoup de lacunes sur ce sujet.

Cordialement
Lolote83

 

[Hecatonchire]

Bonjour,


Tu as la commande Pivoter pour faire le job
(je n'ai pas trop travaillé la présentation)

 

[chris]

Bonjour

Un poil plus court (je suis rasoir...)

let
    Source = Excel.CurrentWorkbook(){[Name="Tab_ListeOrigine"]}[Content],
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(Source, "CODE", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Num", "P"}),
    #"Fractionner la colonne par position" = Table.SplitColumn(#"Fractionner la colonne par délimiteur", "P", Splitter.SplitTextByPositions({0, 2}, false), {"P", "N"}),
    #"Colonne dynamique" = Table.Pivot(#"Fractionner la colonne par position", List.Distinct(#"Fractionner la colonne par position"[P]), "P", "TEXTE"),
    AjColCode = Table.AddColumn(#"Colonne dynamique", "Code", each [Num] & "#" & [N]),
    #"Autres colonnes supprimées" = Table.SelectColumns(AjColCode,{"Code", "FR", "US", "AL"})
in
    #"Autres colonnes supprimées"

Cela me rappelle quand je traitais des fichiers du même genre en plus de 30 langues...

 

[Lolote83]

Bonjour à tous,
Je vais étudier vos solution afin de comprendre la démarche suivie.
Vu que l'on ne peut pas mettre résolut sur 2 postes, j'ai pris le 1er.
Merci à vous
Cordialement
Lolote83

 

[job75]

Bonjour à tous,

Pour ceux qui comme moi ne sont pas des adeptes de Power Query voici une solution VBA :

Sub TestMAJ()
Dim dlig As Object, dcol As Object, liste$, tablo, i&, x$, L%, y$, formule$
Set dlig = CreateObject("Scripting.Dictionary")
Set dcol = CreateObject("Scripting.Dictionary")
liste = "Tab_ListeOrigine"
tablo = Evaluate(liste).Resize(, 2) 'matrice, plus rapide
For i = 1 To UBound(tablo)
    x = tablo(i, 1)
    If x <> "" Then
        L = Len(CStr(Val(x)))
        y = Mid(x, L + 2, 2)
        dlig(Left(x, L) & "#" & Mid(x, L + 4)) = ""
        dcol(y) = ""
    End If
Next
'---restitution---
Application.ScreenUpdating = False
With [Tab_Result].ListObject 'tableau structuré
    If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete xlUp 'RAZ
    If .ListColumns.Count > 1 Then .Range.Columns(2).Resize(, .ListColumns.Count - 1).Delete xlToLeft
    If dcol.Count Then .Range(1, 2).Resize(, dcol.Count) = dcol.keys 'en-têtes
    If dlig.Count Then .Range(2, 1).Resize(dlig.Count) = Application.Transpose(dlig.keys) 'Transpose est limitée à 65536 lignes
    formule = "=IFERROR(VLOOKUP(SUBSTITUTE([@CODE],""#"",""-""&" & .Range(1, 2).Address(1, 0) & ")," & liste & ",2,0),"""")"
    If dlig.Count * dcol.Count Then .Range(2, 2).Resize(dlig.Count, dcol.Count) = formule
End With
End Sub

Il serait facile de remplacer les formules par leurs valeurs mais autant les laisser, ça ne mange pas de pain.

Au lieu du bouton on peut aussi faire exécuter la macro par une Worksheet_Activate.

A+

 

[job75]

La macro du post #5 s'exécute en 13 ms chez moi.

La requête d'Hecatonchire en 2,6 secondes à la 1ère actualisation, en 1,7 ms ensuite.

 

[Hecatonchire]

Pour le plaisir, par formule v365

=LET(t;Tab_ListeOrigine;
c_1;CHOISIRCOLS(t;1);
pre;TEXTE.AVANT(c_1;"-");
lg;GAUCHE(TEXTE.APRES(c_1;"-");2);
suf;STXT(TEXTE.APRES(c_1;"-");3;9);
code;pre&"#"&suf;
PIVOTER.PAR(code;lg;CHOISIRCOLS(t;2);TABLEAU.EN.TEXTE;;0;;0))

 

[Cousinhub]

La macro du post #5 s'exécute en 13 ms chez moi.

La requête d'Hecatonchire en 2,6 secondes à la 1ère actualisation, en 1,7 ms ensuite.

Hello Job,
J'ai eu, moi aussi, la frénésie du " T = Timer"... (forcément suivi du Timer - T...) et je ne le renie pas.
Puis, finalement, se dire que remplir la mission en 0.0001 ou en 1 seconde, ben, la remplir, c'était déjà bien...
Pour moi, les "powerquistes", on essaie effectivement de diminuer les temps de résolution, mais le principal reste de donner une solution..

Pour ceux qui comme moi ne sont pas des adeptes de Power Query voici une solution VBA :

Et étant "adepte" (sans aucune connotation sectaire, juste ayant eu le courage de m'y être "converti"), actuellement, je trouve vraiment PQ comme une solution d'avenir.
Faut juste essayer...
Bonne soirée

 

[job75]

Salut Cousinhub,

Mesurer les durées d'exécution permet de comparer les différentes solutions.

C'est surtout utile sur les (très) grands tableaux où les durées peuvent être rédhibitoires.

A+

 

[Cousinhub]

Salut Cousinhub,

Mesurer les durées d'exécution permet de comparer les différentes solutions.

C'est surtout utile sur les (très) grands tableaux où les durées peuvent être rédhibitoires.

A+

Effectivement,
Mais prendre 1 seconde ou 4 secondes d'exécution, où se situe l'avantage? (perso, je n'ai jamais vu de différences "rédhibitoires", comme tu le dis)
Pour info, (mais tu n'es pas sans le savoir), de plus en plus d'entités interdisent le VBA...
Donc, pourquoi persister dans cette vision unique?
Bonne soirée

 

[job75]

VBA peut faire des choses que PQ ne sait pas faire.

Il est finalement assez simple mais sans doute utilisé trop souvent par des gens incompétents, ce qui peut être dangereux.

C'est peut-être pour ça que des entreprises préfèrent bloquer ou limiter son utilisation.

 

[chris]

Bonjour

VBA peut faire des choses que PQ ne sait pas faire

Cela n'a strictement rien à voir

PowerQuery s'inscrit avant tout dans la familles des requêteurs, outils incontournables dans l'exploitation des bases de données sur lesquelles reposent l'essentiel de toutes les applications, de gestion comme scientifiques.

L'utilisation d'Excel à beaucoup évolué depuis la 1ère mise sur le marché d'un tableur et de plus en plus d’utilisateurs d'Excel traite des tableaux qui ne sont que des tables assimilables à celles des bases de données, d'où l'intérêt d'avoir un bon outil pour les gérer...

Ce qu'est PowerQuery, dont l'articulation avec le tableur a été prise en compte par MS puisqu'il a des possibilités spécifiques par rapport à un requêteur classique.

VBA est un langage de programmation, comme il en existe des dizaines (au moins), dépendant de l'application où il est implémenté et donc non autonome, qui nécessite un apprentissage important et pour ce, également des capacité ou compétences ad hoc (ayant donné des cours en initiation au VBA, je confirme, comme mes collègues, que tous ne seront pas à même d'aller très loin dans la maîtrise).

Et comme tu le soulignes, utilisé par des gens incompétents, non ou peu formés, cela peut faire beaucoup de dégâts...

Pour en revenir à la demande initiale, avec une unique table, les deux approches se valent mais pas sûr que tu aies mis moins de temps à écrire ton code que moi à paramétrer la requête...