Clever-Excel-Forum

Hallo,

ich habe eine große Excel Tabelle mit ca. 1250 Zeilen und rund 50 Spalten, also gut 60.000 Zahlen, die ich nun für eine PowerBI Auswertung anders anordnen muss.

Jede Spalte repräsentiert ein Produkt. Es sind also 50 verschiedene Produkte mit unterschiedlichen Kennlinien.
Die Kennlinien werden durch Formeln ermittelt.
Die Ergebnisse stehen in der gleichen Spalte untereinander angeordnet.
Als oberstes die Drehzahl.
Darunter dann die zu den Drehzahlen korrespondierenden Leistungswerte.
Darunter dann die zu den Drehzahlen korrespondierenden Drehmomentwerte.
Am Ende kommen da Kennlinien heraus, bei denen die Drehzahl auf der X-Achse aufgetragen wird.
Auf der Y1-Achse werden die Leistungswerte aufgetragen.
Auf der Y2-Achse werden die Drehmomentwerte aufgetragen.

Das besondere/spezielle/schwierige ist, dass die drei Achsen in der Tabelle untereinander angeordnet sind.
Es wurde so gemacht, damit die Formeln dann nach rechts rüber kopieret werden können, um dann die 50 verschiedenen Produkte damit zu berechnen, und damit das jederzeit erweiterbar ist.

Damit kommt PowerBI offensichtlich überhaupt nicht klar, daher müssen die Daten anders angeordnet werden.

Und zwar sollen die gesamten Daten in 3 Spalten angeordnet werden: Type, Category und Value.
Das bedeutet, das meine 1250 Zeilen x 50 Spalten Tabelle dann für PowerBI in 3 Spalten und 62.500 Zeilen angeordnet werden.

Genau sowas habe ich vorher noch nie gemacht, meine Versuche mit VERWEIS, SVERWEIS, WVERWEIS, ADRESSE, INDEX, INDIREKT, MTRANS sind bisher kläglich gescheitert.
Ich finde einfach nicht den richtigen Ansatz das umzusetzen.

Bitte keine Diskussion nach der Sinnhaftigkeit oder alternativ Vorschläge.
Ich kann weder die Datenquelle noch die Datenverwendung ändern.
Die Aufgabenstellung ist fest gesetzt: Die vorhandene Anordnung so umzustricken, dass die benötigte Anordnung der Daten erzeugt wird. Nicht mehr und nicht weniger, jede Diskussion ist da leider zwecklos, weil nicht beeinflussbar

Ich kann mich nun natürlich auch doof und dusselig kopieren, aber die Daten sollen ja immer noch durch die ursprüngliche Tabelle gesteuert werden, auch erweitert und verändert werden können.
Durch die Verwendung (irgendeiner) Nachschlagefunktion oder ähnlichem hat man dann nur die Transformation der Anordnung, die dann aus der Ursprungstabelle die Daten übernimmt.

Ich habe eine Beispieldatei angehängt, um das ganze vereinfacht zu darzustellen.
Auf dem Reiter "Daten" ist die Struktur der Rohdaten zu sehen.
Auf dem Reiter "Daten für PowerBI" ist die Struktur zu erkennen, wie sie für PowerBI sein soll. Dort müssten nun Formeln eingetragen werden, um die Daten aus dem Reiter "Daten" zu übernehmen (nachzuschlagen).

Hat jemand eine gute Idee, wie man das umsetzen kann?

Vielen Dank, und beste Grüße,
Chris

Hallo

Meinst du so?

Die Transformation, die mit Power Query durchgeführt wird, kann direkt im Power BI durchgeführt werden. Somit kann  der Zwischenschritt Exel entfallen.

Hallo kkr4 

Ich weiß das auch

Zitat:Ich weiß das auch

Mein Hinweis war an den Fragesteller addressiert, dem da wohl der Durchblick fehlt.

Moin,

vielen Dank für die Antworten!

Wenn es doch direkt in PowerBI geht, dass ist es umso besser.
Nur wie stelle ich das dann genau an?
Muss ich da nur die jeweiligen Codezeilen in PowerBI reinkopieren, oder wie funktioniert das?

Ich bin mir auch nicht sicher, ob das Ergebnis wirklich passt.
Ist die Reihenfolge der Daten denn noch gleich, oder sind die jetzt wild durcheinandergewürfelt? (macht auf mich den Eindruck)

Ich hänge mal einen Ausschnitt der Originaldaten in den Anhang. Vielleicht wird es dann transparenter. Da sind nun auch die Diagramme enthalten, die aus den Daten am Ende in PowerBI erzeugt werden sollen.

Vielen Dank und beste Grüße,
Chris

Hallo,

eine mögliche Formellösung, die Excel 365 voraussetzt sowie dass bspw. LAMBDA und weitere neue Funktionen vorhanden sind.
Die Formel verwendet VSTACK rekursiv, um eine Basismatrix mit den Indizes nach unten hin zu stapeln. Und setzt voraus, dass
die Höhe der Daten für jeden Block 16 Zeilen hoch ist (inkl. Blocküberschrift).

(29.04.2022, 11:29)maninweb schrieb: [ -> ]Hallo,

eine mögliche Formellösung, die Excel 365 voraussetzt sowie dass bspw. LAMBDA und weitere neue Funktionen vorhanden sind.
Die Formel verwendet VSTACK rekursiv, um eine Basismatrix mit den Indizes nach unten hin zu stapeln. Und setzt voraus, dass
die Höhe der Daten für jeden Block 16 Zeilen hoch ist (inkl. Blocküberschrift).

PHP-Code:

=LET(Data;Daten!$B$2:$F$55;Header;Daten!$B$1:$F$1;Blockheight;16;
 vnData;FILTER(Data;LÄNGE(INDEX(Data;0;1))>0);
 vnHeader;INDEX(Header;1;0);
 vnRows;AUFRUNDEN(ZEILEN(vnData)/Blockheight;0)*(Blockheight-1);
 vnBlock;MATRIXERSTELLEN(vnRows;3;LAMBDA(X;Y;LET(N;Blockheight*(AUFRUNDEN(X/(Blockheight-1);0)-1)+1;P;
 WENN(REST(X;Blockheight-1)<1;Blockheight-1;REST(X;Blockheight-1))+1;WAHL(Y;1;N;N+P-1))));
 fnStack;LAMBDA(This;Matrix;Iterator;WENN(Iterator<2;Matrix;This(This;VSTACK(Matrix;Matrix+
 MATRIXERSTELLEN(ZEILEN(Matrix);SPALTEN(Matrix);LAMBDA(X;Y;WENN(Y>1;0;1))));Iterator-1)));
 vnMatrix;fnStack(fnStack;vnBlock;SPALTEN(vnHeader));MATRIXERSTELLEN(ZEILEN(vnMatrix);3;
 LAMBDA(X;Y;LET(N;INDEX(vnMatrix;X;Y);P;INDEX(vnMatrix;X;1);
 WENNFEHLER(WENN(Y>1;INDEX(vnData;N;P);INDEX(vnHeader;1;N));"-"))))) 


Kannst du das in die Excel Datei einbauen und die Excel Datei dann hier anhängen.
Dein Code funktioniert bei mir nicht, keine Ahnung warum?!

Beste Grüße,
Chris

Hallo,

anbei die Datei. Beachte bitte, wie zuvor geschrieben, dass das nur in Excel 365 oder gegebenenfalls in
Excel-Online geht. Nicht in älteren Excel-Versionen, wie z.B. Excel 2013.

Gruß

Hmm, ich würde sagen, das ist Office 365, oder nicht?
[attachment=43096]
Funktioniert leider nicht:
[attachment=43097]