Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

12.11.2024 12:51:46

Camborio

Liebe Community

Trotz zahlreichen Versuchen bin ich bei der untenstehenden Problemstellung nicht zu einem sauberen Resultat gekommen.
Eventuell hat jemand von Euch eine Lösung?
(Weil die Originaltabelle bereits in Power Query mit zahlreichen Befehlen bereinigt und vorbereitet ist, müsste - wenn immer irgendwie möglich - die Lösung in Power Query vorhanden sein.)

Wenn mehrere Datensätze innerhalb der gleichen "KontoNr" und dem gleichen "Ist-Betrag" vorhanden sind, dann soll die erste Ist-Zahl stehen bleiben, alle anderen Ist-Zahlen erhalten eine 0 (Null).

Die Datentabelle für die Ausgangslage und für die Zielsetzung sind in der Beispieldatei ersichtlich.
https://www.herber.de/bbs/user/173543.xlsx

Herzlichen Dank für die Inputs im voraus.

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

12.11.2024 13:37:57

Yal

Hallo Camborio,

Wenn ich davon ausgehen dar, dass die originale Reihenfolge feststeht,
- Du nimmst die Tabelle_Ausgangslage ins Power Query rein
- setze einen Index-Spalte
- mache eine Verweis zu einer zweiten Abfrage
- in der zweite machst Du die Spalte Budget raus,
- Duplikate entfernen auf Basis der Kontonummer, es bleiben normalerweise nur die ersten Einträge
(Achtung: in Zelle D11 hast Du einen "50.000" auf dem Konto 3200, der nicht zu dem "-41.000" passt. Wenn die Werte summiert werden sollten, musst Du "gruppieren nach" anstatt "Duplikat entfernen" verwenden. Index wird mit "min" gruppiert)
- die erste Abfrage wird basierend auf dem Kontonummer und Index gejoint (alle Zeilen von zweiten Tabelle)
- Spalte Kontonummer und Index entfernen
- erweitert werden Kontonummer, Budget und Index
- Reihenfolge mit Index-Spalte einrichten (der Join bringt es gern durcheinander)
- Spalte Index entfernen
- in der Spalte Ist werden die Null durch 0 ersetzt (optional),
- Spaltenreihenfolge einstellen
Fertig

VG
Yal

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

12.11.2024 18:47:00

Luschi

Hallo Cambori,

hier mal meine PQ-Lösung. Wie Du sehen wirst, muß man da schon tiefer mit PQ-M bohren, als es die Möglichkeiten der PQ-Editor-Oberflächen-Memüs hergeben:

https://www.herber.de/bbs/user/173552.xlsx

Gruß von Luschi
aus klein-Paris

PS: Erklärungen zum M;-Code gebe ich natürlich gern, wenn Bedarf besteht

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

12.11.2024 19:52:22

Yal

Hmm... sehr virtuos, aber...

ich füge in den Quelle zwischen Zeile 11 und 12 einen
3200 | -21.000 | 50.000

Die beide Zeilen 3200 | .. | 50.000 kommen ins Ergebnis durch.

VG
Yal

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

13.11.2024 01:17:28

Luschi

Hallo Yal,

auch Dein Einwurf (Mehrfachwerte bei gleicher KontoNr) sind mit PQ-M kein Hexenwerk. Hier mal meine Änderung dazu.

https://www.herber.de/bbs/user/173558.xlsx

Gruß von Luschi
aus klein-Paris

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

13.11.2024 10:26:01

Camborio

Hallo Yal und Luschi

Herzlichen Dank für Eure Unterstützung, die mir sehr weiterhilft, da ich anhand der Beispiel- bzw. Lösungs-Datei alles wunderbar nachvollziehen konnte.
Im Laufe dieser Woche werde ich probieren, Eure Lösung in die Praxisdatei zu 'übertragen' und ich bin frohen Mutes, dass es gelingen wird.

Nochmals vielen lieben Dank für Euren tollen Support.

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

13.11.2024 11:46:24

Yal

Hallo Luschi,

sieht gut aus. Ich schaue gern dein M-Code, weil ich immer neuen Anreiz gewinnen kann.
Ich bin nur unsicher, ob einen PQ-Anfänger damit zurecht kommt, falls minimale Anpassungen gemacht werden sollen.

Ich habe meine vorigen Beschreibung jetzt so geschnitten, dass alles in einer Abfrage passt (mit nebenläufiger Tabelle, was auch nicht Anfänger-Level ist).
Sieht so aus:

let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle_Ausgangslage"]}[Content],

    #"Hinzugefügter Index" = Table.AddIndexColumn(Quelle, "Index", 1, 1, Int64.Type),

    Zwischentabelle = Table.Group(#"Hinzugefügter Index", {"KontoNr", "Ist"}, {{"Index", each List.Min([Index]), type number}}),

    #"Zusammengeführte Abfragen" = Table.NestedJoin( #"Hinzugefügter Index", {"KontoNr", "Index"}, Zwischentabelle, {"KontoNr", "Index"}, "NeueSpalte", JoinKind.LeftOuter),

    #"Entfernte Spalten1" = Table.RemoveColumns(#"Zusammengeführte Abfragen",{"Ist"}),

    #"Erweiterte NeueSpalte" = Table.ExpandTableColumn(#"Entfernte Spalten1", "NeueSpalte", {"Ist"}, {"Ist"}),

    #"Sortierte Zeilen" = Table.Sort(#"Erweiterte NeueSpalte",{{"Index", Order.Ascending}}),

    #"Entfernte Spalten" = Table.RemoveColumns(#"Sortierte Zeilen",{"Index"})

in

    #"Entfernte Spalten"

@Camborio: Am einfachste am ersten Beschreibung halten. Es ist die leichteste Weg in Power Query einzusteigen. Es ergibt 2 Abfragen. Die Unnötige kann man in der Liste der Abfrage in Excel rechts rechtsklicken und bei "Laden in..." auf "nur Verbindung" setzen. Auf diese Art kann man auch die Ergebnis-Tabelle an einer gewünschte Stelle ablegen lassen.

VG
Yal

AW: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

13.11.2024 18:18:05

Luschi

Hallo Yal,

Dein Lösungsweg sieht so einfach aus, aber auch darauf muß man erst mal kommen. Mir war schon klar, daß es über ein Table.NestedJoin() gehen muß - nur wer mit wem??? - jetzt weiß ich es, danke!

Gruß von Luschi
aus klein-Paris

Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

Forumthread: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

Forumthread: Excel PowerQuery: Mehrfachnennungen mit Spezialresultat

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread