Power Query - Gruppieren nach Stufen

Bernd Fuchs · 2024-08-16T11:52:37+00:00

Hallo Excel-Gemeinde, ich habe ein kleines Anliegen, was mir seit einiger Zeit Kopfzerbrechen bereitet. Über PQ frage ich Daten aus einer DB ab, die ungefähr so aussehen. In der Spalte A stehen Nummern, die auch in Spalte B stehen können. Allerdings können nur Nummern in Spalte A stehen, die auch zur Kategorie b gehören. Zirkelbezüge gibt es nicht (1 - 5 / 5 - 1). Ich würde die Liste gerne so transformieren, dass jede Nummer, die auch eine Kategorie b Nummer in Spalte B stehen hat und falls diese auch eine Kategorie b Nummer hat in Spalten nebeneinander angeordnet werden. https://www.herber.de/bbs/user/171653.xlsx Vielen Dank Bernd Fuchs

Hallo Bernd,

... liegt womöglich daran, das die PQ-Profis ein verlängertes WE haben oder Ihnen unklar ist, wie Deine vorgegebene zweite 5 in der "3. Stufe". Ich meine, dass diese für Deine vorgegebenen Datenwerte nicht zutreffend ist. Wenn doch, bedarf es Deiner näheren Erklärung, warum dies so sein soll.

Nachfolgend zunächst mal (m)eine reine Formellösung, die auch in älteren XL-Versionen noch zum Einsatz kommen kann. Wenn es sich jedoch im Original um eine Massendatenauswertung handeln sollte (mehre tausende Datensätze), dann ist eine PQ-Lösung natürlich die bessere Lösungsvariante.

Nachfolgende Formel für Daten bis z.B. max Zeile 999 einfach weit genug nach unten und rechts ziehen kopieren.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
4	Nummer	Nummer 2	Kategorie		Nummer (1. Stufe)	2. Stufe	3. Stufe	4. Stufe	5. Stufe
5	1	10	a		1	4	5
6	1	20	a		2	4
7	1	30	a		3	5
8	1	40	a		4	5
9	1	4	b
10	1	60	a
11	1	70	a
12	2	10	a
13	2	70	a
14	2	100	a
15	2	101	a
16	2	4	b
17	3	5	b
18	3	60	a
19	3	70	a
20	4	10	a
21	4	5	b
22	5	100	a
23

Zelle	Formel
E5	=WENNFEHLER(AGGREGAT(15;6;A$5:A$1000/(C$5:C$1000="b")/(ZÄHLENWENN(E$4:E4;A$5:A$1000)=0)/SUCHEN(1;E$4)^0;1);WENNFEHLER(AGGREGAT(15;6;$B$5:$B$1000/($A$5:$A$1000=D5)/($C$5:$C$1000="b")/(ZÄHLENWENNS(E$4:E4;$B$5:$B$1000;D$4:D4;D5)=0);1);""))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...

Hallo Case,

ich habe die folgende Schleife aus Deinem M-Code getestet:

ExSt = List.Accumulate({1..MaxStufe}, StartT, (sIST, akt) =>

        let

            Erweitert = Table.ExpandListColumn(sIST, "Stufe" & Text.From(akt)),

            Join = Table.NestedJoin(Erweitert, {"Stufe" & Text.From(akt)}, Filtb, {"Nummer"}, "NeueStufe"),

            Expand = Table.ExpandTableColumn(Join, "NeueStufe", {"Nummer2"}, {"Stufe" & Text.From(akt + 1)}),

            Entf0 = Table.SelectRows(Expand, each List.NonNullCount(Record.FieldValues(_)) > 0),

            Gruppe = Table.Group(Entf0, {"Nummer"}, {{"Stufe" & Text.From(akt + 1), each List.Distinct(List.RemoveNulls(Record.Field(_, "Stufe" & Text.From(akt + 1)))), type list}}),

            Umbenannt = Table.RenameColumns(Gruppe, {{"Nummer", "NummerTemp"}}),

            JoinedTable = Table.Join(sIST, "Nummer", Umbenannt, "NummerTemp", JoinKind.LeftOuter),

            OhneN = Table.RemoveColumns(JoinedTable, {"NummerTemp"})

        in

            OhneN),

Und zwar so, daß nur der 1. Durchlauf von List.Accumulate() simuliert wird:

    akt = 1,

    sIST = StartT,

    Erweitert = Table.ExpandListColumn(sIST, "Stufe" & Text.From(akt)),

    Join = Table.NestedJoin(Erweitert, {"Stufe" & Text.From(akt)}, Filtb, {"Nummer"}, "NeueStufe"),

    Expand = Table.ExpandTableColumn(Join, "NeueStufe", {"Nummer2"}, {"Stufe" & Text.From(akt + 1)}),

    Entf0 = Table.SelectRows(Expand, each List.NonNullCount(Record.FieldValues(_)) > 0),

    Gruppe = Table.Group(Entf0, {"Nummer"}, {{"Stufe" & Text.From(akt + 1),

        each List.Distinct(List.RemoveNulls(Record.Field(_, "Stufe" & Text.From(akt + 1)))), type list}}),

    Umbenannt = Table.RenameColumns(Gruppe,{{"Nummer", "NummerTemp"}}),

    JoinedTable = Table.Join(sIST, "Nummer", Umbenannt, "NummerTemp", JoinKind.LeftOuter),

    OhneN = Table.RemoveColumns(JoinedTable, {"NummerTemp"}),

Beim PQ-Schritt: Gruppe = Table.Group... erscheint in der Spalte 'Stufe2' überall 'Error' mit der Fehlerbeschreibung:
Expression.Error: Ein Wert vom Typ "Table" kann nicht in den Typ "Record" konvertiert werden.
ABER: der nächste PQ-Schritt: Umbenannt = Table.RenameColumns(Gruppe,{{"Nummer", "NummerTemp"}})
behebt erstaunlicher Weise diesen Fehler in der Spalte 'Stufe2'
Erfolgt die Umbenennung vor der Gruppierung, dann ist beim Einzeltest alles i.O.
Also so:

Erweitert = Table.ExpandListColumn(sIST, "Stufe" & Text.From(akt)),

Join = Table.NestedJoin(Erweitert, {"Stufe" & Text.From(akt)}, Filtb, {"Nummer"}, "NeueStufe"),

Expand = Table.ExpandTableColumn(Join, "NeueStufe", {"Nummer2"}, {"Stufe" & Text.From(akt + 1)}),

Entf0 = Table.SelectRows(Expand, each List.NonNullCount(Record.FieldValues(_)) > 0),

Umbenannt = Table.RenameColumns(Entf0, {{"Nummer", "NummerTemp"}}),

Gruppe = Table.Group(Umbenannt, {"NummerTemp"}, {{"Stufe" & Text.From(akt + 1),

    each List.Distinct(List.RemoveNulls(Record.Field(_, "Stufe" & Text.From(akt + 1)))), type list}}),

JoinedTable = Table.Join(sIST, "Nummer", Gruppe, "NummerTemp", JoinKind.LeftOuter),

OhneN = Table.RemoveColumns(JoinedTable, {"NummerTemp"}),

Und aus diesem Grund habe ich auch im PQ.Schritt 'ExSt' diese Umbenennung angepaßt. Aber hast Du eine Erklärung für dieses Verhalten?

Gruß von Luschi
aus klein-Paris

Power Query - Gruppieren nach Stufen

Forumthread: Power Query - Gruppieren nach Stufen

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Power Query - Gruppieren nach Stufen

Forumthread: Power Query - Gruppieren nach Stufen

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread