Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 14:05:48
Makro in aktueller Zelle ausführen mit Doppelklick
25.04.2024 12:56:25
Werte von Kunden und Artikel stehen untereinander
25.04.2024 12:55:42
Funktionsparameter auslesen
25.04.2024 11:49:37
Array verwenden
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1884to1888
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1884to1888
1884to1888
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

PQ: Abgleich Header, fehlende ergänzen

PQ: Abgleich Header, fehlende ergänzen
17.06.2022 10:26:25
Chris94
Hallo zusammen,
ich habe eine allgemeine PQ-Frage:
Ist es möglich, dass man innerhalb der PQ-Abfrage Spalten einfügen lässt, wenn diese nicht bereits vorhanden sind - Abgleich vorhandener Headernamen.
Es existieren quasi Spalten mit Headername 1, 1,5 und 2,5 und ich möchte aber sicherstellen, dass alle Zahlen von 1 - 3, inkl. ",5" als Spalten vorhanden sind (1, 1,5, 2, 2,5, 3).
Kann ich dann variabel, je nachdem, welche Spalten bereits vorhanden sind, die anderen (in diesem Fall: 2 und 3) einfügen lassen?
Ist das zu komplex für PQ oder geht das irgendwie? Wenn es gehen sollte und jemand einen Ansatz hat, würde ich mich sehr freuen, wenn er diesen teilen würde.
Vielen Dank im Voraus!
Gruß,
Chris

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ja, das ist mit PQ realisierbar ...
17.06.2022 10:41:12
neopa
Hallo Chris,
... stell doch mal eine kleine Beispieldatei mit ein paar Dummy-Datensätzen einstellst, wo Du Dein IST und händisch (ohne PQ) Dein Ziel darstellst.
Gruß Werner
.. , - ...
AW: PQ: Abgleich Header, fehlende ergänzen
17.06.2022 11:16:15
ChrisL
Hi Chris
In einer Query-Logik macht es eigentlich keinen Sinn Datenfelder ohne Daten zu generieren. Darum kann ich die Nachfrage von Werner gut nachvollziehen.
Dennoch hier mal eine Lösung mittels benutzerdefinierter PQ-Funktion:
https://www.herber.de/bbs/user/153589.xlsx
Quelle:

= fxSpalten(Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],0)
fxSpalten:

(InputTB as table, loop as number) =>
let
txtLaufnummer = Text.Replace(Number.ToText(loop / 2 + 1),".",","),
OutputTB =
if Table.HasColumns(InputTB, txtLaufnummer) then
InputTB
else
Table.AddColumn(InputTB, txtLaufnummer, each null),
Output =
if loop = 4 then
OutputTB
else
@#"fxSpalten" (OutputTB, loop + 1)
in
Output
cu
Chris
Anzeige
AW: PQ: Abgleich Header, fehlende ergänzen
18.06.2022 10:14:31
Luschi
Hallo PQ-Fan's,
hier mal meine Version - Anmerkungen dazu in der Datei:
https://www.herber.de/bbs/user/153619.xlsx
Gruß von Luschi
aus klein-Paris
AW: hmm ...
18.06.2022 11:49:56
neopa
Hallo Luschi,
... Deine Konfiguration macht die Spaltendefinition zwar flexibel aber kann so ja eh nur für Spaltennamen genutzt werden, die Zahlenwerte darstellen sollen. Warum also nicht gleich eine spez. Datentabelle mit den Spaltenbezeichnung in Excel erfassen und diese in eine Hilfsabfrage packen.
Deine Aussage: "Da die Spaltenüberschriften reine Zahlenwerte sind, streiken manchen datenbankähnliiche Funktionen von PowerQuery" ist nicht ganz korrekt. Wahre Ursache dessen ist nämlich, dass die Datenwerte in den Kopfzeile von formatierten Tabellen von Excel zwingend stets als Textwerte interpretiert werden. Dies auch wenn sie ursprünglich als Zahlenwerte formatiert waren. Und von diesem Textformat geht natürlich auch PQ aus.
Deine Lösung kommt nicht ohne M-Code Kenntnisse aus. Offensichtlich bist Du noch nicht auf die Funktion "Überschriften als erste Zeile verwenden" gestoßen. Mit deren Hilfe und der Hilfsabfrage ist eine PQ-Lösung völlig interaktiv erstellbar.
oT: Im übrigen ist die von Dir aufgezeigte Ergebnisdarstellung mit nur zwei einfachen kleinen Formeln viel einfacher und schnell erstellt.
Z.B. in N1: =WENN(MAX(M$4:M4)&lt4;"";0,5+SPALTE(A1)/2) und nach rechts ziehend kopieren
und in N2: =WENNFEHLER(INDEX(tab_Daten;;VERGLEICH(N$4&"";tab_Daten[#Kopfzeilen];0));"") und diese nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: ergänzend dazu ...
18.06.2022 12:44:14
neopa
Hallo Luschi,
... hier noch der M-Code für meinen PQ-Lösungsvorschlag zu Deiner aufgezeigten Aufgabenstellung, welcher ausschließlich durch Einsatz der (in meiner XL2016er Excel-Version) vorhandenen Funktionsaufrufe mit erzeugt wurde:

let
Quelle = Excel.CurrentWorkbook(){[Name="tab_Daten"]}[Content],
#"Tiefer gestufte Header" = Table.DemoteHeaders(Quelle),
#"Transponierte Tabelle" = Table.Transpose(#"Tiefer gestufte Header"),
#"Angefügte Abfrage" = Table.Combine({#"Transponierte Tabelle", neu_Sp_Nrn}),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Angefügte Abfrage",{{"Column1", type text}}),
#"Entfernte Duplikate" = Table.Distinct(#"Geänderter Typ", {"Column1"}),
#"Sortierte Zeilen" = Table.Sort(#"Entfernte Duplikate",{{"Column1", Order.Ascending}}),
#"Transponierte Tabelle1" = Table.Transpose(#"Sortierte Zeilen"),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Transponierte Tabelle1", [PromoteAllScalars=true])
in
#"Höher gestufte Header"
Worin neu_Sp_Nrn} die Hilfsabfrage ist, welche lediglich nur das Einlesen der Excel-Datentabelle aller vorgegebenen Spaltennummern ist, die da als Überschrift die Bezeichnung: "Column1" hat.
Gruß Werner
.. , - ...
Anzeige
AW: ergänzend dazu ...
18.06.2022 14:08:16
Luschi
Hallo Werner,
habe Deine Version getestet und sie macht, was sie soll, ohne in die Tiefen von 'M' einzusteigen. Dann habe ich die Anzahl Datenzeilen auf auf 50.000 erhöht. Auf meiner Kraftmaschine (Intel I9-10900 mit 32 GB RAM und Office 2019 auf M.2 NVMe-PCIe SSD) macht sich eine Zeitverzögerung nicht ganz so bemerkbar - wie das auf einem Büro-Standard-PC/Laptop ist teste ich nächste Woche.
Das 2-malige Transponieren kann bei solch großen Tabellen nicht der Sinn der Sache sein, dazu zählt bei mir auch das UnPivot und zurückpivotieren. Beim Erhöhen auf 100.000 DS war diese Verzägerung auch bei mir bemerkbar und wurde lästig.
Wenn man bedenkt, daß weniger als die Hälfte der möglichen M-Befehle per PQ-Menü realisierbar sind, bleibe ich bei meiner Taktik, mehr Möglichkeiten durch M-Befehle zu realisieren.
Gruß von Luschi
aus klein-Paris

Anzeige
AW: es überrascht mich nicht wirklich, ...
18.06.2022 17:34:39
neopa
Hallo Luschi,
... dass man durch den optimierten Einsatz der vorhandenen M-Funktionen die Effektivität einer Lösung insbesondere bei Massendatenauswertung steigern kann.
Etwas überraschend für mich ist jedoch, dass selbst bei Deiner Kraftmaschine ein spürbarer Zeitaufwand schon bei 100.000 Datensätzen vorhanden sein soll. Wenn dem so ist, dann würde mich jetzt mal interessieren, ob mein Formelvorschlag (wären ja für Deine Beispiel 800.000 notwendige Formeln) nicht sogar "effektiver" wäre, Dies z.B. mal getestet wenn sich von den vorhandenen 4 Spaltenbezeichnungen zwei geändert werden.
Unabhängig von vorbezeichneter schon nicht unwichtiger Effizienzbetrachtung, würde ich annehmen, dass in den Foren wohl zumindest weit über 90% der Fragesteller meist nur nach Lösungsansätzen suchen, für Auswertungen von max. wenigen tausend Datensätzen. Für eine Profillösung ist natürlich auch die Effizienz einer solchen wichtig. Wer nach solchen sucht, sollte dies nicht von Forumshilfe erwarten, sondern sollte sich auch einen Profi(lösung) leisten (können).
Gruß Werner
.. , - ...
Anzeige
AW: PQ: Abgleich Header, fehlende ergänzen
21.06.2022 10:00:06
ChrisL
Hi Luschi
Danke für deinen Input.
Hier ein weiterer Vorschlag:
https://www.herber.de/bbs/user/153689.xlsx
Deine Idee über eine Konfiguration zu arbeiten, habe ich aufgenommen. Die Liste habe ich wie folgt generiert:

let
min = List.First(QuellePflichtfelder[Pflichtspalten]),
max = List.Last(QuellePflichtfelder[Pflichtspalten]),
step = List.First(QuelleSchrittweite[Schrittweite]),
Quelle = List.Numbers(min,((max-min)/step)+1,step)
in
Quelle
Entscheidend ist, dass nun eine Liste als Spaltendefinition verwendet wird. Dadurch wird die Funktion allgemein verwendbar. Der Datentyp (Zahlenreihe oder Text) ist egal z.B.

= fxSpalten(QuelleDaten,MeineListe,0)
oder

= fxSpalten(QuelleDaten,{"ein","2,5","Test"},0)
Die überarbeitete Funktion:

(InputTB as table, InputList as list, loop as number) =>
let
txtSpalte = Text.From(List.First(List.Range(InputList,loop,1))),
OutputTB =
if Table.HasColumns(InputTB, txtSpalte) then
InputTB
else
Table.AddColumn(InputTB, txtSpalte, each null),
Output =
if loop = List.Count(InputList)-1 then
OutputTB
else
@#"fxSpalten" (OutputTB, InputList, loop + 1)
in
Output
cu
Chris
Anzeige
AW: PQ: Abgleich Header, fehlende ergänzen
22.06.2022 16:25:30
Luschi
Hallo ChrisL,
danke für Deine Anwort, ivh bsdtle immer noch an dem Problem:
= Table.ReorderColumns(Quelle, {"1", "1,5", "2", "2,5", "3", "3,5", "4", "4,5"}) denn hier streikt PQ,
wenn man statt {"1", "1,5", "2", "2,5", "3", "3,5", "4", "4,5"} 'MeineListe' angibt, die ich vorher natürlich noch sortiert habe.
Table.ReorderColumns erwartet richtige Textwerte in der Liste, erkennt aber die Listenwerte von 'MeineListe' als Zahlenwerte; deshalb ja die umständliche Umbenennung in meiner Lösung. Habe schon 1 Idee, denn das 2-malige Transponieren von großen Tabellen kann ja auch nicht die wahre Lösung sein. Da freunde ich mich lieber mit dem rekursiven Aufruf der Funktion 'fxSpalten' an.
Gruß von Luschi
aus klein-Paris

Anzeige
AW: PQ: Abgleich Header, fehlende ergänzen
22.06.2022 16:50:54
ChrisL
Hi Luschi
Table.AddColumn erwartet einen Text als Spaltentitel, was ich über Text.From erzeuge. Entsprechend interpretiere ich, dass Spaltentitel sowieso immer Texte sind.
Die Lösung hat bei mir ohne Probleme funktioniert. Jedoch gibt es die Unsicherheit, dass ich Punkt als Dezimaltrennzeichen verwende. Somit schätze ich, dass Komma bei mir dann sowieso als Text interpretiert wird.
Komisch war, dass mit deiner Datei die Zahlenreihe bei mir mit Kommas raus kam, obwohl ich eigentlich Punkt erwartet hätte. Darum in der letzten Version auch nicht mehr Punkt durch Komma ersetzt.
Die Neusortierung sollte eigentlich klappen, ausser es fehlt generell ein Titel. Schau mal ob da nicht vielleicht doch ein Punkt als Dezimaltrennzeichen reingerutscht ist.
cu
Chris
Anzeige
AW: PQ: Abgleich Header, fehlende ergänzen
22.06.2022 16:56:01
Luschi
Hallo ChrisL,
die Lösung war einfacher als gedacht:
- die Abfrage 'Meine Liste' aus Deiner Lösung sieht jetzt so aus: 

let
min = List.First(QuellePflichtfelder[Pflichtspalten]),
max = List.Last(QuellePflichtfelder[Pflichtspalten]),
step = List.First(QuelleSchrittweite[Schrittweite]),
Quelle = List.Numbers(min,((max-min)/step)+1,step),
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"In Tabelle konvertiert", "Spalte1", each Text.From([Column1])),
#"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Column1"}),
Spalte1 = #"Entfernte Spalten"[Spalte1]
in
Spalte1
- damit stehen jetzt Textwerte in der Liste
und die Abfrage 'Ergebnis' ist jetzt dynamisch:

let
Quelle = fxSpalten(QuelleDaten,MeineListe,0),
#"Neu angeordnete Spalten" = Table.ReorderColumns(Quelle, MeineListe)
in
#"Neu angeordnete Spalten"
Gruß von Luschi
aus klein-Paris
Anzeige
AW: PQ: Abgleich Header, fehlende ergänzen
23.06.2022 08:26:18
ChrisL
Hi Luschi
Jetzt verstehe ich erst das Thema. Gute Idee, die Liste gleich auch zur Sortierung zu verwenden und natürlich hast du recht, dass dann auch der Datentyp vom Listeninhalt relevant wird.
Viel zu optimieren gibt es jetzt nicht mehr. Trotzdem noch ein letzter Hinweis bzw. die Kürzung um 1 Zeile. Für die Typenkonvertierung benötigst du nicht unbedingt eine Hilfsspalte.

let
min = List.First(QuellePflichtfelder[Pflichtspalten]),
max = List.Last(QuellePflichtfelder[Pflichtspalten]),
step = List.First(QuelleSchrittweite[Schrittweite]),
Quelle = List.Numbers(min,((max-min)/step)+1,step),
#"In Tabelle konvertiert" = Table.FromList(Quelle, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Geänderter Typ" = Table.TransformColumnTypes(#"In Tabelle konvertiert",{{"Column1", type text}}),
Column1 = #"Geänderter Typ"[Column1]
in
Column1
cu
Chris
AW: PQ: Abgleich Header, fehlende ergänzen
17.06.2022 11:27:37
Chris94
Hallo Werner, hallo Chris,
Es ist absolut richtig, dass es ohne weitere Infos relativ sinnlos wirkt eine Spalte zu generieren, ohne Daten.
Ich möchte lediglich fixe Spaltenbezüge dadurch erreichen, da mein weiterführender VBA-Code sich auf statische Spaltenpos. bezieht. Eine Anpassung meines VBA Codes wäre ein riesen Aufwand und ich bin selbst nicht gut genug, um den Code variabler zu machen - Bezug auf Headername statt fixe Spaltenpos.
Das nur als kurze Erläuterung, hoffe es ist klar, was ich meine.
Anbei die Beispieldatei meines vorliegenden Problems. Dabei kommt noch ein Kriterium hinzu - hat für mich kein Sinn gemacht das jetzt noch abzugrenzen, was ursprünglich mein Gedanke war.
https://www.herber.de/bbs/user/153591.xlsx
Danke für euer Interesse und eure Hilfsbereitschaft, ich schätze das wirklich sehr!
@Chris, ich werde mir deine Lösung gleich ansehen - vielen Dank!
Gruß,
Chris
AW: eine reine interaktive Lösung dafür ...
17.06.2022 12:16:19
neopa
Hallo Chris,
... könnte z.B. wie folgt aussehen.
Sieh mal hier: https://www.herber.de/bbs/user/153596.xlsx
Wobei die Spaltenüberschriften natürlich noch vorhanden sind (sind lediglich gemäß Deiner Zielvorgabe benutzerdefiniert "ausformatiert")
Geht sicherlich noch kürzer zu definieren aber ist erst einmal (m)ein Vorschlag.
Gruß Werner
.. , - ...
AW: PQ: Abgleich Header, fehlende ergänzen
17.06.2022 12:35:01
Chris94
Hallo Chris, hallo Werner,
vielen Dank euch beiden nochmals für eure Hilfe.
@Chris: deine Lösung läuft einwandfrei, vielen Dank Dir - echt toll, hätte ich so nie hinbekommen!
@Werner: auch deine Lösung läuft super, großes Dankeschön! Du hast mir zwei wichtige Ansätze geliefert, auf die ich leider nicht selbst gekommen bin, die mir aber in Zukunft helfen werden solche Problematiken selbst zu lösen.
a) die zu erweiternden Infos einfach in einen separate Tabelle und dann entsprechend zusammenfügen.
b) Werte einer Spalte simpel ersetzen. Hier hab ich deine Lösung noch angepasst (du hast Wert von "1-" und "2-" in 2 Schritten ersetzt. Um flexibler/variabler zu bleiben, da am Ende auch mehr Zahlen betroffen sein werden, habe ich die "Wert ersetzen" einfach auf ersetze "-" durch ",5" angepasst.
Euch ein schönes Wochenende!
Gruß,
Chris
AW: bitteschön, wünsche Dir Gleiches owT
17.06.2022 12:36:47
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige