XL 2021 Power Query somme des fin de journée où il y a juste une heure de commencement ?

[nunizgb]

Bonjour

J'ai mis dans power query plusieurs source afin d'avoir qu'une table que je peut utiliser dans mon fichier excel et cela marche mais j'ai une Erreur quand j'ai 1 heure de commencement et la fin dans la même journée puis une autre qui commence dans la même journée mais fini le lendemain
Donc j'ai les bonne heures calculer pour celle qui commence et fini la même journée mais pour celle qui commence cette même journee mais fini lendemain.
J'ai d'abord les heures pour la journée et ensuite celle pour lendemain à partir de minuit.

Alors que je voudrais avoir le total depuis le commencement de la journée d'hier.

Donc j'ai créer une colonne personnalise avec la formule Table.AddColumn(#"Lignes filtrées", "Personnalisé", each if [PS] = #time(0,0,0) then Date.AddDays([DATE FS], -1) else [DATE FS])

Afin d'avoir pour des journée où la colonne PS est égale à 0 la date FS soit jour - 1.
Donc pour la journée 5.06.22 j'ai d'abord
PS : 4:55 puis FS 10:50 ensuite PS : 19:55 et FS le 6.6.22 à 2:10
Donc avec cette colonneje voudrais avoir
5.6.22 PS 4:55 FS 10:55 ==> Amplitude 5:55
5.6.22 PS 19:55 FS : 2:10 ==> Amplitude 4:04+2:10=6:14
6.6.22 PS 0:00 FS 0:00

Donc comment écrire en power query code qui cherche la cellule dans la colonne PS qui est égale à 0 puis modifie la date si FS est différent de 0 afin d'avoir la même date que J-1. Puis ensuite faire la somme FS de la ligne où PS = 0 avec FS de J-1 de la dernier PS de la journée en question/
Je ne sais pas si je me suis bien exprimer.
PS: Dans la même journée calendrier peux avoir uniquement 2 PS et 2 FS ou 1 FS et l'autre FS J+1

Merci pour votre aide

 

[Cousinhub]

Bonjour,
Il eût été beaucoup plus simple avec un petit fichier exemple.
Par exemple, si tes données sont conformes avec ton image, les dates dans une colonne distincte des heures, tu peux mettre cette formule (nom des colonnes à adapter)

CalculAmplitude = Table.AddColumn(#"Type modifié", "Amplitude", each Time.From((Number.From([date fs])+Number.From([heure fs]))-(Number.From([date ps])+Number.From([heure ps]))))

Sinon, joindre un fichier permettra de mieux t'aider
Bonne fin d'apm

 

[nunizgb]

Merci pour ta réponse et quand j'ai mis les dates J-1 pour cette journée cela calcul bien

5/6/22 ps 4:55 fs 10:55 amplitude 5:55
5/6/22 Ps 19:55 5/6/22 23:59 amplitude 4:04
5/6/22 Ps 0:00 5/6/22 2:10 amplitude 2:10
Alors que je voudrais

5/6/22 ps 4:55 fs 10:55 amplitude 5:55
5/6/22 Ps 19:55 5/6/22 2:10 amplitude 6:14 service conduite etc....
6/6/22 Ps 0:00 6/6/22 0:00 amplitude0:00 et aussi pour les autres colonnes

Je joins un fichier test où j'ai mis les informations que pour un mois et dans un onglet j'essaye de faire cela avec power query mais je n'arrive pas alors que sur l'autre onglet avec la formule SI j’obtient bien le calcul mais quand ps est supérieur à fs avec la formule :
=SIERREUR(SI([@PS]=0;SOMME((D3+[@FS])-B3);SI([@PS]>[@FS];MOD([@FS]-[@PS];1);[@FS]-[@PS]));MOD([@FS]-[@PS];1))

Par contre je dois trouve aussi comment calculer la même chose pour autres colonne horaire conduite etc... est-ce que je vais une ligne de code pour chaque colonne où on peut faire une ligne de code qui regroupe aussi les autres colonnes

Merci pour votre aide

En clair j'ai mis que la date de ps et fs soient la même date et non plus avec le décalage

 

[Cousinhub]

Bonjour,
Je ne comprends pas pourquoi tu scindes ta ligne enjambant minuit en 2?
Si tu as une prise de service le 05/06/2013 à 22H00 et une fin de service le 06/06/2013 à 05H00, pourquoi tu mets en 2?
C'est manuel, ou l'extraction d'un quelconque logiciel?

 

[nunizgb]

Merci en fait c'est d'abord extraction d'un logiciel qui affiche comme cela dans le logiciel quand j'exporte vers le fichier excel.

Quand il y a une prise de service par exemple 4/6/22 à 19:00 et fin de service le 5/6/22
dans le fichier excel j'ai une ligne pour 4/6/22 de 19:00 jusqu'à 23:59
puis une autre ligne pour 5/6/22 de 00:00 à 2:10

Et donc pour chaque ligne le logiciel calcul amplitude travail etc..

Mais comme je le met dans un autre fichier ces informations avec la formule Filtre avec choisir les colonnes a transférer sur le fichier excel par par exemple pour pour prise de service j'ai deux colonne (mais en réalite cela remplir tous les informations de la table :
et donc je met
=FILTRE(CHOISIR({1.2};tab_test[PS];tab_test[PS]*24;tab_test[DATE PS]=$A13;0)

Mais les heures de travail quand il y a ps 0:00 ne sont pas comptabilise pour la journée precedant et donc cela fausse calcul par exemple pour HS de la semaine

 

[Cousinhub]

Re-,
OK, vu. Par contre, est-ce que tu pourrais mettre une base de donnée "type", i-e sans formule, juste ce que sort ton logiciel, sans données confidentielles, bien sûr, mais avec quelques cas typiques?
@ te relire

 

[nunizgb]

Ok je vais faire cela ce soir le fichier comment le logiciel exporte mais je vais juste ellargie des colonne et lignes parce qu'on voit rien.
Et donc mois je copie ce mois dans un fichier ou j'ai 12 mois pour l'année et ensuite dans ce fichier teste je met tous les fichiers de ce dossier où se trouvent ces années.
Afin que dans mon fichier je n'est qu'une formule pour chaque mois de chaque année.
Avant je faisait une formule pour chaque mois pour chaque année avec recherchev et recherche x

 

[Cousinhub]

Re-,
Euh, on va y aller pas à pas, chaque problème à la fois...
Donc, un premier tableau "type", et ensuite, on continue...
Bonne apm

 

[nunizgb]

Bonjour

Voilà j'ai fait u fichier exemple où j'ai mis fichier extrait depuis le logiciel dans un onglet, j'ai juste efface les informations confidentiel ainsi que des données qu'il ne sont pas importer puis j'ai juste agrandir les lignes et colonnes afin que cela se voit mieux.

Puis dans un autre onglet j'ai mis le fichier où je copier ensuite ses donnes afin d'avoir la meilleur mise en page

Ensuite dans 3 j'ai mis le tableau final (fichier) où toutes les donnes de différents source sont alors consolider.

Cela marche impeccable sauf quand il y a 2 pris de service le même jours mais une fin de service et J+1 et donc je fait manuellement ajout de lignes pour la semaine et le calcule des heures (somme) afin.

Donc j'ai mis dans 4 onglet extrait brut d'un autres mois où il y a aussi 2 prise de service le même et dont une fin de service fini aussi J+1 mais cette fois c'est sur la même ligne parce que J+1 a aussi une prise de service et fin de service

Merci pour l'aide afin que cela ce fasse automatiquement et que je ne dois plus faire manuellement pour X personnes

 

[Cousinhub]

Bonjour,
C'est tout simplement incompréhensible, ton charabia.
Fais l'effort de bien construire tes phrases, stp...
Je t'avais demandé un tableau, tu m'en donnes 4, avec des explications oiseuses...
Tu mets dans le 1er onglet "extraction brut logiciel" que ce sont les données brutes extraites du logiciel
Et dans le 4ème onglet "A", la même explication.
Tu mets que les colonnes ne sont pas toujours au même endroit, mais sont-elles toujours nommées de façon identique?
Tu penses que c'est lors de 2 pointages dans la même journée qu'il semblerait que les lignes soient scindées...
Ne serait-ce plutôt lors d'un changement de semaine?
Mais comme il n'y a qu'un cas, difficile de s'en faire une idée (d'autant que dans l'onglet A, on est en septembre, et dans le premier, en juin...)
Tu comprends ton fichier, mais pas nous...
On essaie de refaire?
Juste 1 onglet, avec extraction brute, mais comprenant plusieurs cas (et aussi les noms de colonnes)
Et (comme je l'ai précisé supra), on fait du pas-à-pas...

 

[nunizgb]

Merci
Je vais faire qu'un onglet mais le problème c'est qu'avec le logiciel on peux qu'extraire un mois par mois et qu'ensuite le logiciel nomme le fichier document.xls avec un onglet A.
Dans cet onglet il n'y a pas de nom de colonne parce que en haut par exemple où il y a indiqué dans une ligne Amplitude sera tte etc..
Il y a d'autres lignes avec des informations confidentielles et après par exemple colonne repos il y a d'autres colonne mais qui ne sont pas extraire.

Donc je peux bien mettre dans un seul onglet extrait du logiciel mois par mois mais qui sera un copié coller du onglet A que j'ai mis.

Sinon je peux mettre le fichier final où sont regroupés tous les mois et des années avec un seul onglet et non de colonnes qui utilise pour le fichier final. Mais ce ne sont alors donne brut du logiciel mais que j'ai consolider avec power query avin d'avoir tous les mois et années dans un seul fichier et dans un tableau.

Dans exemple que j'ai fourni pour moi de juin c'est le dimanche et lundi pas de prise de service
Pour le mois septembre c'est samedi mais le dimanche il y a une prise de service.
Donc je fais mettre un fichier avec 1 onglet mais c'est copier coller des valeur du fichier extrait du logiciel pour 4 mois où il y avait 2 prise de service le même jour

 

[nunizgb]

Bonjour
Voici le fichier tableau excel avec un seul onglet où j'ai consolider plusieurs fichier source du logiciel afin d'avoir un (les donnes brut extrait du logiciel sont copie coller dans un fichier annuel avec 12 onglet pour chaque mois). Donc ce tableau exemple que je fourni ici sont consolidation de plusieurs fichier annuelle afin d'avoir qu'un seul fichier annuellement)
J'ai mis juste pour exemple onglet A comment sont les donnes brut extrait depuis ficher logiciel

Puis quand j'ai dis que des donnes issue du logiciel quand sont extrait ne sont pas jamais dans la même colonne. Par exemple dans le fichier précédent on voit bien que pour le mois juin les valeur pour travail sont dans la colonne Q alors que pour mois de septembre sont R

 

[Cousinhub]

Re-,
Onglet A :
- Les colonnes à droite sont également issues du logiciel? Il n'y a aucun calcul de ta part?
- S'il faut remettre en 1 ligne les cas où l'heure de FS est à 23H59, il faut également additionner les valeurs des colonnes de droite?

 

[nunizgb]

Re-,
Ok, je vais essayer à partir du fichier déjà fourni, et travailler essentiellement sur l'onglet "A"
Et tu en dis quoi, sur le moment de rajout de ligne, avec un horaire de fin à 23H59?
Ce ne serait pas bien lors d'un changement de semaine?

Bonjour nos message ce sont croiser

Donc j'ai mis un fichier où il y a tableau finale avec consolidation de plusieurs fichiers extrait du logiciel.
Donc en clair je dois trouver une solution automatique dans l'onglet que j'ai mis pas celui extrait brut du logiciel A.

Dans exemple que j'ai mis dans le dernier fichier dans le mois de juin, août et septembre il y a 2 prise de service le même jour.

En clair voici arborescence de mes dossiers :
Fichier finale power query sous dossier A
Dossier A ==> consolidation de tous les fichier annuelle du extrait du logiciel power querry sous dosser B
Dossier B ==> fichiers annuelles des extrait mensuel du logiciel (pas donnes brut mais copie coller)

Et tu en dis quoi, sur le moment de rajout de ligne, avec un horaire de fin à 23H59? ==> En fait c'est uniquement qu'il faut ajouter une ligne pas dans ce fichier mais dans le fichier final donc cela ce n'est pas important pour le moment parce que il faut uniquement ajouter une ligne si 2 prise de service le même jour Vu que mon fichier final il y a que 7 ligne pour chaque jours

Ce ne serait pas bien lors d'un changement de semaine? ==> Peut être mais je n'est qu'un exemple où il y a 2 prise de service le même jour et qu'une fin de service se trouve J+1 et que ce jour J+1 il n'y a pas de prise de service. Donc je ne sais pas si c'est un BUG du logiciel et s'il vont le corrige ou non

 

[nunizgb]

Re-,
Onglet A :
- Les colonnes à droite sont également issues du logiciel? Il n'y a aucun calcul de ta part?
- S'il faut remettre en 1 ligne les cas où l'heure de FS est à 23H59, il faut également additionner les valeurs des colonnes de droite?

Onglet A du dernier fichier sont bien les donnes issue du logiciel aucun calcul de ma part j'ai juste supprimer les informations confidentiel et supprimer les autres colonne qui ne servant a rien

Quand la FS est à 23:59 le JOUR et PS 0:00 le JOUR +1 alors il faut bien additionner (FS Jour + FS jour +1) - PS Jour dans la ligne où se trouve FS 23:59 afin d'avoir amplitude. Puis ajoute Conduite où se trouve FS 23:59 + conduite jour +1 où se trouve Ps 0:00 afin d'avoir le total conduite et aussi pour les autres valeur colonne travail tte etc..
Puis dans la ligne où se trouve PS 0:00 mettre tous les valeur à 0:00 dans tous les colonne afin d'avoir 0 pour cette journée.

Désole si je n'est pas bien expliquer