Power query pour un fichier Excel
Bonjour,
J'ai besoin de votre aide pour un fichier excel à transformer en Power Query.
Sur ce fichier j'ai un tableau (voir onglet Fichier de base) ce tableau sort d'un power query entre plusieurs fichier.
Sur ce tableau, il y a la société, le matricule, nom prénom du salarié et des absences "MALAD" ou autres avec des dates. Le Power query ressort avec des écarts entre TEAMS et OCTIME car sur TEAMs les dates de l'absence se font sur deux lignes (prendre l'exemple du nom prénom 1 et 3) tandis qu'Octime fait une seule ligne car l'absence est en continue.
J'aimerai (voir onglet Fichier attendu après PQ), qu'a la place des deux lignes dans TEAMS, il y est qu'une seule ligne avec l'arrêt sur la période et non deux lignes alors que les dates sont en continue.
Cependant il faut bien garder les deux lignes quand l'arrêt à une coupure entre les deux dates comme le nom prénom 4 qui à des dates d'arrêts discontinues.
Si possible me donner le code M ou comment faire pour que j'intègre dans le POWER QUERY déjà existant (tableau fichier de base) pour que le contrôle se fasse directement et que je ne repasse pas dans un autre POWER QUERY pour faire le contrôle.
Si besoin de plus de précision, n',hésitez pas.
Merci beaucoup,
Bien cordialement
Bonjour,
Voici une solution, qu'il faudra adapter à ton fichier. (PS, je n'ai pas tenu compte des colonnes "OCTIME", mais on peut modifier)
N'hésite pas, si tu n'y arrives pas
Bonne journée
Bonjour,
Merci je vais regarder, oui il faudrait faire la même chose à coté pour Octime car dans mon power query de base si les deux sont équivalent, ils ressortent pas dans le contrôle.
Pouvez-vous faire aussi pour ça ?
Re-,
... si les deux sont équivalent, ils ressortent pas dans le contrôle.
Je ne comprends pas trop
Faut-il ressortir toutes les lignes, ou seulement les lignes qui ne correspondent pas?
Désolée je n'ai pas été clair...
Je viens de tester et ce que vous m'avez fait est bon mais il me faudrait aussi la même chose pour le coté Octime.
Je m'explique, dans mon power query, il y a un contrôle de faire entre Teams et Octime et les erreurs entre les deux ressortent comme dans le fichier que je vous ai envoyé.
En suivant ce que vous avez fait, je n'ai plus les données côté Octime.
Il faudrait vraiment que le fichier qui sort du power query soit identique au deuxième onglet que je vous ai envoyé, une partie teams et la partie Octime associé pour que ce qui sorte ce sont que les différences entre les deux cotés.
Désolée si ce n'est pas très clair.
Re-,
Dans le fichier joint, un essai.
Si la date de fin est différente, la ligne passe en jaune
Si ça convient...
J'ai testé, le power query ressort mais je n'ai plus les dates de teams :
| Société | Matricule | Nom et prénom | TEAMS motif | TEAMS Libellé | OCTIME Motif | OCTIME Date Début | OCTIME Date Fin |
Re-,
Est-ce que tu pourrais mettre tout le code M que tu utilises, stp? (juste le code)
Sers-toi de la balise de codes pour l'insérer
Hello une proposition un peu sale mais très facile à comprendre si besoin
@+
Voici le code M qui enlève les dates de teams...
Merci :)
let
// =====================================================
// Requête initiale
// =====================================================
Source = Table.NestedJoin(
#"Extrac Resultat absences Teams M et M-1",
{"Matricule"},
#"Abs paie mais pas octime",
{"Matricule"},
"Abs paie mais pas octime",
JoinKind.LeftOuter
),
#"Colonnes supprimées" = Table.RemoveColumns(
Source,
{
"Bulletin","PAIE_Code","Libellé","PAIE_Début","PAIE_Fin",
"Nb. jrs. calendaires","Nb. jrs. ouvrés","Nb. jrs. ouvrables",
"Nb. jrs. travaillés","Date bulletin","Clé"
}
),
#"Abs paie mais pas octime développé" =
Table.ExpandTableColumn(
#"Colonnes supprimées",
"Abs paie mais pas octime",
{"PAIE_Code","Libellé","PAIE_Début","PAIE_Fin","Nb. jrs. calendaires"},
{
"Abs paie mais pas octime.PAIE_Code",
"Abs paie mais pas octime.Libellé",
"Abs paie mais pas octime.PAIE_Début",
"Abs paie mais pas octime.PAIE_Fin",
"Abs paie mais pas octime.Nb. jrs. calendaires"
}
),
#"Requêtes fusionnées" =
Table.NestedJoin(
#"Abs paie mais pas octime développé",
{"Matricule"},
#"Contrôle Non importés",
{"MMAT"},
"Contrôle Non importés",
JoinKind.LeftOuter
),
#"Contrôle Non importés développé" =
Table.ExpandTableColumn(
#"Requêtes fusionnées",
"Contrôle Non importés",
{"MMOTIF","DDEB","DFIN","NBH","Nb. jrs. calendaires"},
{
"Contrôle Non importés.MMOTIF",
"Contrôle Non importés.DDEB",
"Contrôle Non importés.DFIN",
"Contrôle Non importés.NBH",
"Contrôle Non importés.Nb. jrs. calendaires"
}
),
#"Colonnes renommées" =
Table.RenameColumns(
#"Contrôle Non importés développé",
{
{"Abs paie mais pas octime.PAIE_Code", "TEAMS motif"},
{"Abs paie mais pas octime.Libellé", "TEAMS Libellé"},
{"Abs paie mais pas octime.PAIE_Début", "TEAMS date début"},
{"Abs paie mais pas octime.PAIE_Fin", "TEAMS date fin"},
{"Abs paie mais pas octime.Nb. jrs. calendaires", "TEAMS nb de jours"},
{"Contrôle Non importés.MMOTIF", "OCTIME Motif"},
{"Contrôle Non importés.DDEB", "OCTIME Date Début"},
{"Contrôle Non importés.DFIN", "OCTIME Date Fin"},
{"Contrôle Non importés.NBH", "OCTIME Nb d'H"},
{"Contrôle Non importés.Nb. jrs. calendaires", "OCTIME Nb de jours calendaires"}
}
),
#"Colonnes supprimées1" =
Table.RemoveColumns(
#"Colonnes renommées",
{
"OCTIME Nb de jours calendaires",
"TEAMS nb de jours",
"OCTIME Nb d'H"
}
),
#"Lignes filtrées1" =
Table.SelectRows(
#"Colonnes supprimées1",
each [Société] <> "LABM_DELAPORTE"
and [Société] <> "Laboratoire_SYNLAB_Bioliance"
),
// =====================================================
// Regroupement des périodes consécutives
// =====================================================
#"Lignes vides supprimées" =
Table.SelectRows(
#"Lignes filtrées1",
each not List.IsEmpty(
List.RemoveMatchingItems(
Record.FieldValues(_),
{"", null}
)
)
),
#"Rempli vers le bas" =
Table.FillDown(
#"Lignes vides supprimées",
{"Société","Matricule","Nom et prénom","TEAMS motif","TEAMS Libellé"}
),
TypeDate0 =
Table.TransformColumnTypes(
#"Rempli vers le bas",
{
{"TEAMS date début", type date},
{"TEAMS date fin", type date},
{"OCTIME Date Début", type date},
{"OCTIME Date Fin", type date},
{"Matricule", type text}
}
),
OCTime =
Table.Distinct(
Table.SelectColumns(
TypeDate0,
{
"Société",
"Matricule",
"OCTIME Motif",
"OCTIME Date Début",
"OCTIME Date Fin"
}
)
),
AjtDates =
Table.AddColumn(
TypeDate0,
"Date de début consolidée",
each
if [TEAMS date début] = null
or [TEAMS date fin] = null
then null
else List.Dates(
[TEAMS date début],
Duration.Days([TEAMS date fin] - [TEAMS date début]) + 1,
#duration(1,0,0,0)
),
type list
),
#"Lignes dates null supprimées" =
Table.SelectRows(
AjtDates,
each [Date de début consolidée] <> null
),
ExpandDates =
Table.ExpandListColumn(
#"Lignes dates null supprimées",
"Date de début consolidée"
),
AjtIdx =
Table.AddIndexColumn(
ExpandDates,
"Index",
0,
1,
Int64.Type
),
GroupBy =
Table.Group(
AjtIdx,
{
"Société",
"Matricule",
"Nom et prénom",
"TEAMS motif",
"TEAMS Libellé",
"Date de début consolidée",
"Index"
},
{
{
"Date de fin consolidée",
each List.Max([Date de début consolidée]),
type date
}
},
GroupKind.Local,
(s,c)=>
Number.From(
c[Index]-s[Index]
<>
Duration.Days(
c[Date de début consolidée]
-
s[Date de début consolidée]
)
or c[Matricule] <> s[Matricule]
)
),
SupprCol =
Table.RemoveColumns(
GroupBy,
{"Index"}
),
TypeDate1 =
Table.TransformColumnTypes(
SupprCol,
{
{"Date de début consolidée", type date},
{"Date de fin consolidée", type date}
}
),
FusionOcTime =
Table.NestedJoin(
TypeDate1,
{
"Société",
"Matricule",
"TEAMS motif",
"Date de début consolidée"
},
OCTime,
{
"Société",
"Matricule",
"OCTIME Motif",
"OCTIME Date Début"
},
"OcTime",
JoinKind.FullOuter
),
Expand =
Table.ExpandTableColumn(
FusionOcTime,
"OcTime",
{
"OCTIME Motif",
"OCTIME Date Début",
"OCTIME Date Fin"
},
{
"OCTIME Motif",
"OCTIME Date Début",
"OCTIME Date Fin"
}
)
in
ExpandDans mon fichier de base j'ai ce code là, et moi je veux ce code + rajouter que quand les dates sont consécutives dans teams elles deviennent qu'une ligne et non deux pour ne pas qu'elle ressortent en erreur car dans Octime ce n'est qu'une ligne.
Mais que les dates qui ne sont pas consécutives avec des trous entre deux dates continues de ressortir en erreur...
Je ne sais pas si c'est pas plus simple de vous envoyez le code M d'origine :
let
Source = Table.NestedJoin(#"Extrac Resultat absences Teams M et M-1", {"Matricule"}, #"Abs paie mais pas octime", {"Matricule"}, "Abs paie mais pas octime", JoinKind.LeftOuter),
#"Colonnes supprimées" = Table.RemoveColumns(Source,{"Bulletin", "PAIE_Code", "Libellé", "PAIE_Début", "PAIE_Fin", "Nb. jrs. calendaires", "Nb. jrs. ouvrés", "Nb. jrs. ouvrables", "Nb. jrs. travaillés", "Date bulletin", "Clé"}),
#"Abs paie mais pas octime développé" = Table.ExpandTableColumn(#"Colonnes supprimées", "Abs paie mais pas octime", {"PAIE_Code", "Libellé", "PAIE_Début", "PAIE_Fin", "Nb. jrs. calendaires"}, {"Abs paie mais pas octime.PAIE_Code", "Abs paie mais pas octime.Libellé", "Abs paie mais pas octime.PAIE_Début", "Abs paie mais pas octime.PAIE_Fin", "Abs paie mais pas octime.Nb. jrs. calendaires"}),
#"Requêtes fusionnées" = Table.NestedJoin(#"Abs paie mais pas octime développé", {"Matricule"}, #"Contrôle Non importés", {"MMAT"}, "Contrôle Non importés", JoinKind.LeftOuter),
#"Lignes filtrées" = Table.SelectRows(#"Requêtes fusionnées", each true),
#"Contrôle Non importés développé" = Table.ExpandTableColumn(#"Lignes filtrées", "Contrôle Non importés", {"MMOTIF", "DDEB", "DFIN", "NBH", "Nb. jrs. calendaires"}, {"Contrôle Non importés.MMOTIF", "Contrôle Non importés.DDEB", "Contrôle Non importés.DFIN", "Contrôle Non importés.NBH", "Contrôle Non importés.Nb. jrs. calendaires"}),
#"Colonnes renommées" = Table.RenameColumns(#"Contrôle Non importés développé",{{"Abs paie mais pas octime.PAIE_Code", "TEAMS motif"}, {"Abs paie mais pas octime.Libellé", "TEAMS Libellé"}, {"Abs paie mais pas octime.PAIE_Début", "TEAMS date début"}, {"Abs paie mais pas octime.PAIE_Fin", "TEAMS date fin"}, {"Abs paie mais pas octime.Nb. jrs. calendaires", "TEAMS nb de jours"}, {"Contrôle Non importés.MMOTIF", "OCTIME Motif"}, {"Contrôle Non importés.DDEB", "OCTIME Date Début"}, {"Contrôle Non importés.DFIN", "OCTIME Date Fin"}, {"Contrôle Non importés.NBH", "OCTIME Nb d'H"}, {"Contrôle Non importés.Nb. jrs. calendaires", "OCTIME Nb de jours calendaires"}}),
#"Colonnes supprimées1" = Table.RemoveColumns(#"Colonnes renommées",{"OCTIME Nb de jours calendaires", "TEAMS nb de jours", "OCTIME Nb d'H"}),
#"Valeur remplacée" = Table.ReplaceValue(#"Colonnes supprimées1",null,"-",Replacer.ReplaceValue,{"OCTIME Motif", "OCTIME Date Début", "OCTIME Date Fin"}),
#"Lignes filtrées1" = Table.SelectRows(#"Valeur remplacée", each ([Société] <> "LABM_DELAPORTE" and [Société] <> "Laboratoire_SYNLAB_Bioliance"))
in
#"Lignes filtrées1"Hello,
Soit on repart de ton code et on rajoute un bloc soit tu nous donnes une vision de la source des données avant toutes les manipulations et tu nous expliques brièvement les règles pour atteindre ton objectif.
Ce sera plus simple je pense
@+
Bonjour,
J'ai déjà envoyé ce que j'aimerai au final mais rien ne fonctionne donc je pense qu'il vaut mieux partir de mon code M de base et rajouter un bloc.
La finalité c'est que ce fichier doit être un fichier de contrôle des écarts entre teams et octime :
- si les dates sont identiques entre teams et octime alors ça ne ressort pas dans le fichier
- si les dates ne sont pas identiques ça ressort dans le fichier
Le problème c'est que les dates teams sortent en deux lignes (01-05 au 15/05 et après 16/05 au 31/05 alors que dans les lignes octime ce n'est qu'une ligne 01/05 au 31/05) j'aimerai que quand teams à ça, ça sorte en 1 date et donc ne ressort pas dans le fichier car pas d'écart.
Mais que si teams à deux dates qui ne sont pas consécutives (01-05 au 15/05 et 25/05 au 31/05) alors celle la on les garde en écart...
Re,
Du coup c'est ce qui a été fait.
Qu'est ce qui ne convient pas ici du coup ? Tu as bien les différentes lignes ainsi qu'une colonne qui te dit où sont les écarts entre teams et octime non ?
@+