Power Query: Excel-Tabelle mit Zellformaten einlesen - geht das?

[RaiSta]

Hallo Ihr Wissenden,

wie kann ich in Power Query beim Einlesen einer *.xlsx- oder *.xlsm-Datei nicht nur die Werte der Zellen einlesen, sondern auch gleich deren Zahlenformate berücksichtigen?
Dabei geht es mir NICHT um so Dinge wie Farbe, Fett, Kursiv, ... sondern um die Zahlenformate - also wenn eine Zelle z.B. mit einem Text formatiert ist wie folgt:

Zellenwert: "4711" (der Wert in der Zelle, mit dem ich rechnen könnte - wenn es sinnvoll wäre)
Zellenanzeige:  "Re.Nr.: 4711" (das, was der Benutzer sieht, wenn er die Tabelle öffnet)

erweitert auf nicht-numerischen Inhalt:
Zellenwert: "4711-0815"
Zellenanzeige:"Re.Nr.: 4711-0815"

In PQ möchte ich somit "Re.Nr.: 4711"  bzw. "Re.Nr.: 4711-0815" erhalten - geht das? Wie?

Danke für Eure Hilfe,
RaiSta

[schauan]

Hallöchen,

eventuell geht das nicht, da lasse ich mich aber gerne eines Besseren belehren.
Alternativ - indem Du in PQ den Zusatz neu definierst oder die Daten als csv ausgibst und die csv einliest - da ist das dabei.

[ws-53]

In PQ möchte ich somit "Re.Nr.: 4711"  bzw. "Re.Nr.: 4711-0815" erhalten - geht das? Wie?

Da ja mit dem ETL-Tool Power Query die Daten für die spätere Präsentation aufbereitet werden, somit PQ ja nicht die Präsentationsebene ist, verstehe ich nicht wofür das gut sein sollte.

Aber mit scheinbar sehr viel Wissen über den inneren Aufbau von Excel, hat es Confy bereits 2016 geschafft, Fraben auszulesen.

Extract Cell Color with M

Insofern müsste es auch möglich sein, Zahlenformate auszulesen.

Besser wäre es jedoch, wenn den Hintergrund deiner Frage und das damit verbundene, eigentliche, Ziel erläuterst. Denn damit steigt die Chance, dass du eine zielführende Antwort bekommst.

[DeltaX]

Da ja mit dem ETL-Tool Power Query die Daten für die spätere Präsentation aufbereitet werden, somit PQ ja nicht die Präsentationsebene ist, verstehe ich nicht wofür das gut sein sollte.

Genau richtig. Wofür soll es gut sein, Formate einzulesen?

[Ralf A]

Wenn ich Dich richtig verstehe, willst Du ja einfach nur ein "ReNr: " vor 4711, 4711-0815 oder was auch immer setzen. Wobei 4711 eben eine Zahl und 4711-0815 Text ist. Macht doch nix... 
In Spalte Nummer steht 4711, Rechnung ist die neue Spalte...

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(Quelle, "Rechnung", each "ReNr: " & Text.From([Nummer]))
in
    #"Hinzugefügte benutzerdefinierte Spalte"

Wobei ich vermute, dass die Performance besser sein könnte, wenn die Spalte erst als Text formatiert wird und bei der neuen Spalte nicht jede Zeile einzeln umgewandelt werden muss (Originalspalte vorher komplett in Text umwandeln):

let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Nummer", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ" , "Rechnungsnummer", each "ReNr: " & [Nummer])
in
    #"Hinzugefügte benutzerdefinierte Spalte"

Die alte Originalspalte kannst Du ja danach löschen...

[RaiSta]

Zunächst mal vielen Dank für die diversen Lösungsansätze.

Natürlich ist es richtig, dass PQ sich um die Werte und nicht um die Formatierung kümmert - wie es Datenbanken nun mal so machen... Allerdings verwende ich in XL gerne Formatierungen, die abhängig vom Inhalt diesen oder jenen Text für den Benutzer erzeugen. Das ist in XL ja - innerhalb gewisser Grenzen - problemlos über die Zellenformatierung zu erledigen (und in XL habe ich ebenfalls noch keinen guten Weg - ohne VBA - gefunden, auch ein derartiges Zellformat per Formel auszulesen).

Z.B. lese ich derzeit mit einer PQ-Abfrage extern erstellte Tabellen ein, die Variationen zu bestimmten Berechnungen enthalten (in der ersten Spalte (z.B. "A5:A704") die Abszissenwerte, nachfolgend ("B5:B704") die Spalte der Ergebnisse mit Original-Einstellungen und anschließend("C5:C704" ... "AX5:AX704" - oder was auch immer - die Anzahl der genutzen Spalten als auch der Zeilen variiert ebenfalls von Datei zu Datei...)  dann Werte, die sich ergeben, wenn die Ausgangsbedingungen um bestimmte Werte verändert werden).

In der Zeile oberhalb der Werte ist jeweils notiert, um welchen Betrag und welche Einheit die darunterstehende Spalte gegenüber der Ausgangsspalte verändert ist, meist entweder in "%" oder in "nm". Das fange ich z.B. mit dem Zahlenformat "[>=1]0,0?"nm";[>0]0,0?%;;@" ab. Wenn der Wert also größer oder gleich 1 ist, steht da das Format in der Einheit "nm", wenn kleiner, dann steht da ein Prozentwert. Analog verhält es sich mit der Re.Nr., die im Original auch nur ne Zahl oder halt ein Text ist, die gemäß obigem Schema dann passend in 'lesbaren' Zellinhalt umgesetzt werden.

Könnte ich in PQ das Zahlenformat einlesen (und woanders wieder einfügen, ggf auch einfach als Text), wäre es entsprechend einfacher, die Abfrage auszunutzen. So muss ich jetzt an zwei Stellen das Gleiche machen - was immer die Gefahr birgt, dass in der Zieltabelle dann der Ausgangszustand doch irgendwann mal nicht sauber widergegeben wird.

Natürlich gibt's dafür auch irgendwelche Umwege, das zu lösen - aber am schönsten wäre es derzeit für mich, das Format direkt auslesen zu können. Daher meine Frage, durchaus im Bewusstsein, dass hiermit vielleicht eine Paradigmen-Verletzung beabsichtigt wird... Die 'fest-verdrahtete' Form von Ralf ist ein schöner Ansatz, aber zu starr und daher leider nur bedingt hilfreich - es geht auch nicht um 'unendlich viele' Zellen, halt nur um eine vergleichsweise kleine variable Anzahl an Zellen mit variablen Zahlenformaten... Der Link von ws-53 dagegen scheint durchaus vielversprechend ... nur müsste ich mich da im Forum anmelden - oder meine Anmeldedaten wieder finden... weiß nicht, ob ich da schon mal aktiv war - und die Lösung scheint doch 'recht komplex' zu sein ... mit direktem Zugriff auf die xml-Datei zur XL-Datei.... Ist vielleicht aber auch erforderlich, wenn ich derart 'Fremdartiges' in PQ anstrebe. Mal sehen, ob ich den Zeitaufwand gestemmt kriege...

Gruß und Dank allen Antwortenden - hatte die Hoffnung schon fast aufgegeben, noch ne Antwort zu erhalten...,
RaiSta

[schauan]

Hallöchen,

1)
auf den Versuch einer Anmeldung dort im Forum kam leider nix
2)
ein(ig)e Verlinkung(en) mosert mein Virenscanner an

[ws-53]

und die Lösung scheint doch 'recht komplex' zu sein ... mit direktem Zugriff auf die xml-Datei zur XL-Datei....

Ja, die Lösung ist wohl sehr komplex und im Moment auf das auslesn von Farben getrimmt. Müsste somit noch entsprechend umgebaut werden. 

Um auch die Formate zu erhalten, könntest du die Datei ja mit einem Makro auslesen und in diesem auch die ETL-Schritte durchführen.

[derHoepp]

Hallo,

benutzerdefinierte Zahlenformate werden in einer Excel-Datei in der styles.xml gespeichert. Dazu wird ein numFmt Element erzeugt, dessen formatCode-Attribut den Formatierungsstring enthält (Achtung, mit HTML-Entitäten).
Das Zahlenformat wird anschließend über seine ID je Cell-Style in ein eigenes Xf-Element in das Attribut numFmtId innerhalb der cellXfs-Auflistung eingebunden. Die so erzeugten Zellformatierungen werden nullbasiert hochgezählt. 
In der sheet1.xml wird dann in einem cell-Objekt (c-Element) über das s-Attribut (das steht wohl für Style) ein Bezug zum zugehörigen Xf-Element hergestellt.

Lange Rede, kurzer Sinn:
Um das benutzerdefinierte Zahlenformat auszulesen musst du 
1. Die Excel-Datei wie ein Zipfile einlesen.
2. Den Inhalt des NumFmt mit seiner ID auslesen.
3. Die cellXfs-Elemente in der richtigen Reihenfolge einlesen.
4. Die Informationen aus 2. Und 3. verbinden.
5. Die Sheet1.xml mit den Informationen zu jedem c Element auslesen und das s-Attribut um die Information aus 4. ergänzen.
Damit nicht genug, um auch die Überschriften zu erhalten musst du 
6. diese noch aus der sharedstrings.xml auslesen.

Alles in allem also Recht aufwändig und wenig flexibel.

Viele Grüße
derHöpp

[Ralf A]

In der Zeile oberhalb der Werte ist jeweils notiert, um welchen Betrag und welche Einheit die darunterstehende Spalte gegenüber der Ausgangsspalte verändert ist, meist entweder in "%" oder in "nm". Das fange ich z.B. mit dem Zahlenformat "[>=1]0,0?"nm";[>0]0,0?%;;@" ab. Wenn der Wert also größer oder gleich 1 ist, steht da das Format in der Einheit "nm", wenn kleiner, dann steht da ein Prozentwert. Analog verhält es sich mit der Re.Nr., die im Original auch nur ne Zahl oder halt ein Text ist, die gemäß obigem Schema dann passend in 'lesbaren' Zellinhalt umgesetzt werden.....

Die 'fest-verdrahtete' Form von Ralf ist ein schöner Ansatz, aber zu starr und daher leider nur bedingt hilfreich - 

...smile... nun ja... fest verdrahtet war es doch nur, durch die statische ReNr.
Das lässt sich doch aber durch eine entsprechende Funktion anpassen und flexibler gestalten.

Bsp. Die Spalte Wert enthält Zahlen, wird in Text umgewandelt, neue Spalte "Geändert' wird erstellt, in welcher alle Werte <= 1 mit % und alles darüber mit km ausgegeben wird. Das kann man natürlich noch beliebig ausbauen (und 'ne Fehlerbehandlung einbauen), doch zur Demonstration ist das ok. Hier der Code für die Tabelle:

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Wert", type text}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Geändert", each fktAuswahl([Wert])),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Wert"})
in
    #"Entfernte Spalten"

Und hier der Code für die (zugegebenermaßen sinnfreie) fktAuswahl:

Code:

(Wert as text)   as text =>
let
    Zahl = Number.FromText(Wert),
    Ergebnis =
    if Zahl <= 1 then
         Wert & " %"
    else
         Wert & " KM" 
in
    Ergebnis