[Powerquery] - Regroupement de données
Hello à tous,
J'ai un tableau qui sort d'un ERP, pas très bien structuré parfois des données sont présentes, parfois non, les joies de la saisie en entrée ....
J'aimerai par PowerQuery avoir le résultat affiché en PJ.
En soit j'ai quelque chose qui fonctionne mais un peu lent sur une grosse volumétrie.
Je continue de plancher sur le sujet et entre temps si vous avez des propositions je prends
Merci d'avance à tous
@+
Edit : désolé je suis allé trop vite j'avais oublié les montants
voici la version corrigée
Bonjour Baroute78,
Est ce que la structure des données est constante ?
Cordialement
Hi,
Pour essayer de faire avancer le schmilblick... Pas sûr que j'y arrive, mais si on essaie pas...
Est-qu'il y a une norme dans l'écriture des différentes rubriques?
- Ex : Projet commence toujours par "Projet", ID?, Rubrique (2 lettres?), Clef (numérique et point, numérique tout seul - qu'on pourrait confondre avec le montant?)
Bonne journée
Hey,
Désolé ce n'était pas assez précis.
Je me suis encore trompé, dans le résultat attendu, mais je pense que vous l'avez compris, le projet C doit être une ligne vide et c'est au projet D que j'affecte les valeurs attendues en ligne Projet C dans le fichier transmis. Cf en PJ la c'est le bon résultat attendu
Concernant les interrogations :
- Projet : contiendra toujours le terme Projet dedans ;
- ID : sera composé à minima de 5 caractères
- Rubrique : 2 lettres uniquement
- Clef : peut être considéré comme du texte il y aura à chaque fois au moins 2 points dedans
- Date : de type datetime
- Montant : ressort en nombre dans l'export (ça enlève le doute avec la clef)
En théorie il n'y a pas de "règle" qui devrait se chevaucher
Merci à tous
@+
Re-,
Un premier jet....
let
Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
GroupBy = Table.Group(Source, {"Données_ERP"}, {{"Tbl", each _}} ,GroupKind.Local , (s,c) => Number.From(Text.StartsWith(Text.From(c[Données_ERP]), "Projet"))),
AjoutRec = Table.AddColumn(GroupBy, "rec", each [a=[Tbl][Données_ERP],
Projet = List.First(List.Select(a, each Text.StartsWith(_,"Projet"))),
ID = List.First(List.Select(a, each Value.Is(_, type text) and Text.Length(_)=5)),
Date = Date.From(List.First(List.Select(a, each Value.Is(_, type datetime)))),
Rubrique = List.First(List.Select(a, each Value.Is(_, type text) and Text.Length(_)=2)),
Clef = List.First(List.Select(a, each Value.Is(_, type text) and Text.Contains(_,"."))),
Montant = List.First(List.Select(a, each Value.Is(_, type number)))])[[rec]],
Expand = Table.ExpandRecordColumn(AjoutRec, "rec", {"Projet", "ID", "Date", "Rubrique", "Clef", "Montant"})
in
ExpandPas sûr pour l'ID, je l'ai limité à 5 caractères...
Edit, en me relisant, peut-être qu'un List.Buffer pour "a" serait bénéfique...
Bonjour à tous,
Une approche IA, donc assez lourde, mais qui peut donner des pistes de résolution.
J'ai supposé que la clé contient des "." pour éviter la confusion "montant/clef" car ça me semble un point de crise important : est-on bien sûr que les montants sont toujours entiers, où alors séparés par "," ? Car sinon entre la clé "1.1" et le montant "1.1" pas facile de faire la différence...
EDIT : l'idée est la meme que celle de @cousinhub, passer par des tests & sauvegarde dans un Record. J'imagine qu'il faut tester sur le vrai jeu de données pour voir si les tests sont "suffisants" où à affiner.
let
Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
Src = Table.TransformColumnTypes(Source,{{"Données_ERP", type any}}),
// ***** Paramètres & Helpers *****
SourceTable = Src,
ColName = Table.ColumnNames(SourceTable){0}, // 1ère (et unique) colonne
AlphaList = List.Transform({65..90}, each Character.FromNumber(_)), // A..Z
DigitList = List.Transform({48..57}, each Character.FromNumber(_)), // 0..9
IsTwoLetters = (txt as text) as logical =>
let s = Text.Upper(Text.Trim(txt))
in Text.Length(s) = 2 and Text.Length(Text.Remove(s, AlphaList)) = 0,
IsClef = (txt as text) as logical =>
let
s = Text.Trim(txt),
parts = Text.Split(s, "."),
valid =
Text.Contains(s, ".") and
List.Count(parts) >= 2 and
List.AllTrue(
List.Transform(parts, each _ <> null and _ <> "" and Text.Length(Text.Remove(_, DigitList)) = 0)
)
in valid,
// Raccourci : prend la partie avant l'espace si le texte contient un " " (pour enlever l'heure)
BeforeSpace = (s as text) as text =>
if Text.Contains(s, " ") then Text.BeforeDelimiter(s, " ") else s,
IsDate_ddMMyyyy = (txt as text) as logical =>
let
s0 = Text.Trim(txt),
s = BeforeSpace(s0),
parts = Text.Split(s, "/"),
okLen = List.Count(parts) = 3
and Text.Length(parts{0}) = 2
and Text.Length(parts{1}) = 2
and Text.Length(parts{2}) = 4,
// DigitList doit déjà être défini comme dans la version précédente
allNum = okLen
and Text.Length(Text.Remove(parts{0}, DigitList)) = 0
and Text.Length(Text.Remove(parts{1}, DigitList)) = 0
and Text.Length(Text.Remove(parts{2}, DigitList)) = 0
in
// soit on reconnait le pattern 02/02/2025, soit un parse DateTime fonctionne
allNum or (try Date.From(DateTime.FromText(s0, "fr-FR")) otherwise null) <> null,
ToDate_fr = (txt as text) as nullable date =>
let
s0 = Text.Trim(txt),
s = BeforeSpace(s0),
d1 = try Date.FromText(s, "fr-FR") otherwise null,
d2 = if d1 <> null then d1 else (try Date.From(DateTime.FromText(s0, "fr-FR")) otherwise null)
in
d2,
IsNumber_fr = (txt as text) as logical =>
let tryN = try Number.FromText(Text.Trim(txt), "fr-FR") otherwise null
in tryN <> null,
// ***** Nettoyage de base *****
Trimmed = Table.TransformColumns(SourceTable, {{ColName, each if _ = null then null else Text.Trim(Text.From(_)), type text}}),
RemoveBlanks = Table.SelectRows(Trimmed, each _ <> null and _ <> ""),
AddIndex = Table.AddIndexColumn(RemoveBlanks, "Index", 0, 1, Int64.Type),
// ***** Détection des entêtes "Projet ..." *****
AddIsProject = Table.AddColumn(AddIndex, "IsProjectRow", each
let s = Text.Lower(Text.Trim(Record.Field(_, ColName)))
in Text.StartsWith(s, "projet"), type logical),
AddProjectName = Table.AddColumn(AddIsProject, "Projet", each if [IsProjectRow] then Record.Field(_, ColName) else null, type text),
FillProject = Table.FillDown(AddProjectName, {"Projet"}),
KeepOnlyWithProj= Table.SelectRows(FillProject, each [Projet] <> null),
// ***** On groupe par Projet, pour classer les lignes de données *****
Grouped = Table.Group(
KeepOnlyWithProj,
{"Projet"},
{
{"Rows", each Table.Sort(_, {{"Index", Order.Ascending}}),
type table [Index=Int64.Type, #"IsProjectRow"=logical, Projet=text]}
,
{"FirstIndex", each List.Min([Index]), Int64.Type}
}
),
// ***** Parsing selon vos règles, par bloc de projet *****
Parsed = Table.AddColumn(
Grouped,
"Parsed",
(grp) =>
let
tbl = grp[Rows],
// Exclure la ligne d'en-tête "Projet ..."
dataRows = Table.SelectRows(tbl, each [IsProjectRow] = false),
values = if Table.RowCount(dataRows) = 0 then {} else Table.Column(dataRows, ColName),
// Accumulateur : on prend la première occurrence par type
accInit = [ ID=null, Date=null, Rubrique=null, Clef=null, Montant=null ],
accRes = List.Accumulate(
values,
accInit,
(state, current) =>
let s = Text.Trim(Text.From(current)) in
if state[Date] = null and IsDate_ddMMyyyy(s) then
Record.TransformFields(state, {{"Date", (d)=> ToDate_fr(s)}})
else if state[Rubrique] = null and IsTwoLetters(s) then
Record.TransformFields(state, {{"Rubrique", (r)=> s}})
else if state[Clef] = null and IsClef(s) then
Record.TransformFields(state, {{"Clef", (c)=> s}})
else if state[Montant] = null and IsNumber_fr(s) then
Record.TransformFields(state, {{"Montant", (m)=> Number.FromText(s, "fr-FR")}})
else if state[ID] = null and not IsDate_ddMMyyyy(s) and not IsTwoLetters(s) and not IsClef(s) and not IsNumber_fr(s) then
Record.TransformFields(state, {{"ID", (i)=> s}})
else
state
),
// Sortie sous forme d'enregistrement
result = [
Projet = Text.Trim(grp[Projet]),
ID = accRes[ID],
Date = accRes[Date],
Rubrique = accRes[Rubrique],
Clef = accRes[Clef],
Montant = accRes[Montant]
]
in
result
),
#"Expanded Parsed" = Table.ExpandRecordColumn(Parsed, "Parsed", {"ID", "Date", "Rubrique", "Clef", "Montant"}, {"ID", "Date", "Rubrique", "Clef", "Montant"}),
Sorted = Table.Sort(#"Expanded Parsed", {{"FirstIndex", Order.Ascending}}),
RemovedAux = Table.RemoveColumns(Sorted, {"Rows","FirstIndex"}),
// Types (culture FR)
Typed = Table.TransformColumnTypes(
RemovedAux,
{
{"Projet", type text},
{"ID", type text},
{"Date", type date},
{"Rubrique", type text},
{"Clef", type text},
{"Montant", type number}
},
"fr-FR"
)
in
TypedHello,
Je voulais éviter d'avoir à faire 6 List.Select mais il se trouve que c'est la version la plus véloce.
Une version avec un seul List.Select pour les curieux
let
Source = Groupe3_Source,
Regles = {
[Type = type text , condition = (x)=>Text.Contains(x,"Projet")],
[Type = type text , condition = (x)=>Text.Length(x) = 5 ],
[Type = type date , condition = (x)=>x=x],
[Type = type text , condition = (x)=>Text.Length(x) = 2],
[Type = type text , condition = (x)=>Text.Contains(x,".")],
[Type = type number , condition = (x)=> x=x]
},
GroupBy = Table.Group(Source, {"Données_ERP"}, {{"tbl", each List.Transform(
Regles ,
(x) => List.First(List.Select(List.Buffer(_[#"Données_ERP"]) , each Type.Is(Value.Type(_),x[Type]) and x[condition](_) ),null))}}
,0,(s,c)=> Number.From(try Text.Contains(c[#"Données_ERP"],"Projet") otherwise 0))[tbl],
Table = Table.FromRows( GroupBy , {"Projet","ID","Date","Rubrique","Clef","Montant"})
in
Table@cousinhub, on était sur la même idée de départ et ça reste la plus véloce vs une version un peu plus épurée. Trop d'appels successifs et de tests sur la même liste vs ta méthode un peu plus directe.
@saboh au final je m'attendais à plus avec 650k lignes
Merci à tous pour les propositions. C'est du quasi instantané sur 650k donc je considère que c'est ok pour moi
@+
Edit : Merci à JFL pour cette petite requête permettant de faire des tests de vélocité
Très élégant le stockage des règles, j'aime bien. Bravo !
Bonjour à tous !
Très largement après la bataille mais pour le "fun", une approche qui me semble véloce (test sur la source répétée 10.000 fois) :
Hey,
Une fois n'est pas coutume, encore surpassé par JFL
J'ai rajouté un List.Buffer sur la liste projet et ça a considérablement augmenter la vélocité.
En soit la différence est à peine notable sur 650k lignes mais bravo au CHEF
Encore merci à tous
@+
Bonjour à tous
une proposition à tester sans Table.Group mais avec Table.Pivot (en considérant que tous les projets ont un nom différent)
comme cousinhub je suis preneur de la requête de JFL pour les tests de vélocité, merci d'avance
let
Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
Projet = Table.AddColumn(Source, "Projet", each
if [Données_ERP] is text and Text.StartsWith([Données_ERP], "Projet") then [Données_ERP] else null),
Règles = Table.AddColumn(Projet, "Règles", each
if [Projet]<>null then "Projet"
else if [Données_ERP] is datetime then "Date"
else if [Données_ERP] is number then "Montant"
else if Text.Select([Données_ERP],".")>"." then "Clef"
else if Text.Length([Données_ERP])>=5 then "ID"
else "Rubrique"),
FillDown = Table.FillDown(Règles,{"Projet"}),
Pivot = Table.Pivot(FillDown, {"ID", "Date", "Rubrique","Clef", "Montant"}, "Règles", "Données_ERP")
in
PivotStéphane
Hello Stéphane,
Yes j'avais checker cette approche de prime abord, mais le Table.Pivot est assez energivore
Merci pour ta solution
@+
Bonjour à tous,
Je suis régulièrement les post en lien avec PQ, et effectivement je rejoins raccourcix et cousinhub, pourriez vous mettre à dispo le fichier permettant de calculer la
vélocité d'une requête ?,
Ce serait très pratique !!
Merci d'avance,
Cordialement,
Bonsoir à tous !
Un petit tour ici ------> Tutoriel Vélocité requêtes