PQ - bestimmte Tag gruppieren

Moderator: ModerationP

PQ - bestimmte Tag gruppieren

Beitragvon PQ Anfrage » 18. Okt 2021, 09:40

Hallo
ich möchte eine Liste mit Tages Umsätzen, das Wochenende (Samstag und Sonntag) immer zum Freitag kummulieren, und das mit PQ
PQ Anfrage
 

Re: PQ - bestimmte Tag gruppieren

Beitragvon RPP63 » 18. Okt 2021, 09:43

Ich möchte im Lotto gewinnen, spiele aber nicht.
Benutzeravatar
RPP63
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 11537
Registriert: 19. Okt 2012, 17:41
Wohnort: mitten im Pott

Re: PQ - bestimmte Tag gruppieren

Beitragvon GMG-CC » 18. Okt 2021, 09:52

Ähnlich kurze Antwort wie Ralf:
A) Was hinder dich daran und
B) Was hast du schon versucht? (Muster-xlsx)
C) Hilfsspalte!
Gruß
Günther (bekennender Power Query-Enthusiast) :!:

Excel-ist-sexy!
Wenn du keine Excel-Version angegeben hast, gehe ich von >=Excel 2016 aus.
Hilfe zur Selbsthilfe, nicht Komplettlösungen für den kommerziellen Einsatz und das "natürlich" gratis!
Benutzeravatar
GMG-CC
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 4902
Registriert: 12. Nov 2011, 23:17
Wohnort: Geesthacht bei Hamburg

Re: PQ - bestimmte Tag gruppieren

Beitragvon PQ Anfrage » 18. Okt 2021, 10:45

habe eine Beispieldatei hochgeladen
Spalten D und E sind in der Originaldatei nicht enthalten
Ziel ist pro Woche, die Tage 5 bis 7 kumuliert auf den Freitag 5 zusammen zu ziehen

Danke
PQ Anfrage
 

Re: PQ - bestimmte Tag gruppieren

Beitragvon Kurt Schütze » 18. Okt 2021, 10:48

anbei die Liste
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Kurt Schütze
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 20
Registriert: 20. Aug 2013, 09:00

Re: PQ - bestimmte Tag gruppieren

Beitragvon GMG-CC » 18. Okt 2021, 12:31

Moin Kurt,
wie schon angedeutet: Mit Hilfsspalte(n) ist das gut lösbar. Mein Ergebnis siehst du im Anhang.
Im Grunde genommen ist das eine Gruppierung in PQ, wobei ich vorher eine Hilfsspalte erstellt habe, wo der Wochentag (Mo .. Do) oder "Wochenende" drin steht.
Wenn in den Rohdaten die Spalten D:E enthalten sind, ist die Auswertung mit PQ für Einsteiger deutlich leichter, es geht aber auch ohne diese Spalten (eventuell dann aber per Bedingte Spalte ohne KaWo und Wochentagin der Ausgabe).
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Gruß
Günther (bekennender Power Query-Enthusiast) :!:

Excel-ist-sexy!
Wenn du keine Excel-Version angegeben hast, gehe ich von >=Excel 2016 aus.
Hilfe zur Selbsthilfe, nicht Komplettlösungen für den kommerziellen Einsatz und das "natürlich" gratis!
Benutzeravatar
GMG-CC
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 4902
Registriert: 12. Nov 2011, 23:17
Wohnort: Geesthacht bei Hamburg

Re: PQ - bestimmte Tag gruppieren

Beitragvon Kurt Schütze » 18. Okt 2021, 13:14

Hallo Günther
Gruppieren auf Hilfsspalte mit dem Wochentag (mit ersetzen von Fr, Sa und So auf Wochenende) komme ich
aber wie bringst du als Wochenende das Freitagsdatum angezeigt
habe die Liste mit meinem Lösungsansatz mal angehängt

Gruss
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Kurt Schütze
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 20
Registriert: 20. Aug 2013, 09:00

Re: PQ - bestimmte Tag gruppieren

Beitragvon DerHoepp » 18. Okt 2021, 14:40

Moin,

endlich geht es mal explizit um Powerquery und es gibt eine Beispieldatei. Warum da der Lösungsweg jetzt nicht dabei sein soll, versteh ich ehrlich gesagt auch nicht. Aber das ist Günthers Entscheidung. Ich würde auch auf eine Hilfsspalte verzichten und direkt alles in PowerQuery machen:
Code: Alles auswählen
let
    Quelle = Excel.CurrentWorkbook(){[Name="tbRohdaten"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Datum", type date}, {"Umsatz", type number}}),
    AggregationsTag = Table.AddColumn(#"Geänderter Typ", "UmsatzTag", each Text.From(Date.WeekOfYear([Datum])) & "_" &
                (if Date.DayOfWeekName([Datum]) = "Samstag" or Date.DayOfWeekName([Datum]) = "Sonntag"
                 then
                    "Freitag"
                 else
                    Date.DayOfWeekName([Datum])
                )),
    Aggregation = Table.Group(AggregationsTag, {"UmsatzTag"}, {{"UmsatzSumme", each List.Sum([Umsatz]), type number}})
in
    Aggregation


Wenn die Richtung stimmt, kannst du natürlich noch eine etwas komplexere Formel zur Ermittlung der ISO-Kalenderwoche erstellen (die ist in M leider noch nicht eingebaut).

Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9841
Registriert: 14. Mai 2013, 11:08

Re: PQ - bestimmte Tag gruppieren

Beitragvon Kurt Schütze » 18. Okt 2021, 15:30

Danke erstmals an derHöpp
wie hat er aber die Spalte H hingekriegt?
Kurt Schütze
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 20
Registriert: 20. Aug 2013, 09:00

Re: PQ - bestimmte Tag gruppieren

Beitragvon DerHoepp » 18. Okt 2021, 15:50

Moin nochmal,

Ich rate, dass einfach der jeweilige Wert des Kalenderdatums von Freitagen, Samstagen und Sonntagen auf den Freitag zurückgerechnet wurde.

Hier übrigens noch eine mögliche Funktion zur Berechnung der ISO-Kalenderwoche:
Code: Alles auswählen
//als Funktion ISO_CalWeek gespeichert. Aufruf in Benutzerdefinierten Feldern mit = ISO_CalWeek([Datumsfeld])
(EingabeDatum as date) as number=>
    let
        WoTaErsterJanuar = Date.DayOfWeek(Date.StartOfYear(EingabeDatum),Day.Monday) +1 ,
        MontagErsteKW = if
                            WoTaErsterJanuar <= 4
                        then
                            Date.AddDays(Date.StartOfYear(EingabeDatum), -(WoTaErsterJanuar + 1))
                        else
                            Date.AddDays(Date.StartOfYear(EingabeDatum), ( 8 - WoTaErsterJanuar)),
        Kalenderwoche =  if
                        EingabeDatum < MontagErsteKW
                    then
                        53
                    else
                        Number.RoundDown( (Number.From(EingabeDatum-MontagErsteKW) / 7)+1  ,0)
    in
        Kalenderwoche


Die Funktion kannst du einfach in einer Abfrage als berechnetes Feld hinzufügen. Im M-Code von meinem obigen Beispiel:
Code: Alles auswählen
let
    Quelle = Excel.CurrentWorkbook(){[Name="tbRohdaten"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Datum", type date}, {"Umsatz", type number}}),
    AggregationsTag = Table.AddColumn(#"Geänderter Typ", "UmsatzTag", each Text.From(ISO_CalWeek([Datum])) & "_" &
                (if Date.DayOfWeekName([Datum]) = "Samstag" or Date.DayOfWeekName([Datum]) = "Sonntag"
                 then
                    "Freitag"
                 else
                    Date.DayOfWeekName([Datum])
                )),
    Aggregation = Table.Group(AggregationsTag, {"UmsatzTag"}, {{"UmsatzSumme", each List.Sum([Umsatz]), type number}})
in
    Aggregation


Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9841
Registriert: 14. Mai 2013, 11:08

PQ_Nur Ergebnis

Beitragvon Gast_01 » 18. Okt 2021, 15:57

Hallo DerHoepp

Warum Günther das macht nur Ohne Lösungsweg zu posten ist doch fast bei jedem in den verschiedenen Foren angekommen.

Das sind die Notgeilen Centfuchser, würde er die Lösung gleich drin lassen würde er kein Cent verdienen mehr.
Und was noch ist es könnte ja ein anderer zu schnell an PQ lernen.
Für mich ist das traurig solche denkweise.
Gast_01
 

Re: PQ - bestimmte Tag gruppieren

Beitragvon Gast » 19. Okt 2021, 12:51

Danke an derHöpp
bei deinem Tipp "die Funktion einfach als Abfrage als berechnetes Feld hinzufügen" musst du mir noch genauer erklären
Gruss
Gast
 

Re: PQ - bestimmte Tag gruppieren

Beitragvon DerHoepp » 19. Okt 2021, 16:27

Hallo,

mit "einfach als berechnetes Feld einfügen" meinte ich, dass du die Funktion einfach im Gleichungseditor beim einfügen einer benutzerdefinierten Spalte nutzen kannst. Das Ergebnis, dass dabei herauskommt, siehst du im letzten Codeauszug im Schritt "AggregationsTag" den aufruf der Funktion ISO_CalWeek([Datum]) als Funktionsaufruf nach dem each von .AddColumn. Weil ISO_CalWeek einen numerischen Wert zurückgibt, ich in meinem Beispiel allerdings einen zusammengesetzten Text erzeuge, wird der Wert dann noch mit Text.From() umgewandelt.

Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9841
Registriert: 14. Mai 2013, 11:08

Re: PQ - bestimmte Tag gruppieren

Beitragvon Kurt Schütze » 03. Nov 2021, 16:17

Hallo derHöpp
habe mal hin und her getestet, ich hätte doch lieber das Datum einzel und den Freitag, Samstag und Sonntag als Wochenende gruppiert. Eigendlich wie es schon in der Spalte H bis K dargestellt ist.
Gruss Kurt
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Kurt Schütze
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 20
Registriert: 20. Aug 2013, 09:00

Re: PQ - bestimmte Tag gruppieren

Beitragvon DeltaX » 03. Nov 2021, 21:53

Anbei eine Lösung ohne Powerquery, dafür Powerpivot
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
DeltaX
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 34
Registriert: 17. Jan 2021, 15:05


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 22 Gäste