Dies ist ein typischer Fall für Self-Service BI mit Excel-Daten.
Vor ein paar Tagen stellte mir ein Kunde folgende Frage:
Ich habe eine Excel-Tabelle mit Zahlen und Textual content in einer Spalte. Ich möchte dieses Blatt in Energy BI importieren und eine Analyse der Zahlen in dieser Spalte durchführen.
Wie kann ich die Zahlen vom Textual content in dieser Spalte trennen?
Denken Sie daran, dass ich auch den Textual content in dieser Spalte benötige.
Ich struggle noch nie in dieser Scenario gewesen, additionally begann ich zunächst mit der Technik, die ich kannte.
Ich habe ein Dummy-Excel mit dem gleichen Drawback erstellt, das so aussieht:
Um einen PoC zu erstellen, habe ich diese Daten zunächst in eine SQL Server-Datenbank geladen, um dort zu sehen, wie ich sie lösen kann.
Lösung des Issues mithilfe von SQL
T-SQL verfügt über zwei Funktionen, die in solchen Szenarien hilfreich sind:
- TRY_CONVERT()
- Dieser versucht, einen Wert in einen Zieldatentyp umzuwandeln. Wenn dies fehlschlägt, wird NULL zurückgegeben.
- ISNUMERIC()
- Prüft, ob ein Wert ein numerischer Wert ist. Wenn ja, wird 1 zurückgegeben. Andernfalls 0.
Basierend auf diesem Wissen habe ich eine Abfrage geschrieben, um die Werte in zwei Spalten aufzuteilen. Eines mit den Zahlen und eines mit dem Textual content:
SELECT (Values)
,TRY_CONVERT(decimal(18, 5), (Values)) AS (Quantity)
,IIF(ISNUMERIC((Values)) = 0, (Values), NULL) AS (Textual content)
FROM (dbo).(MixedValues);Das Ergebnis ist die folgende Tabelle:
Wenn Sie genau hinschauen, sehen Sie, dass Zeile 17 als Textual content erkannt wird.
Dies liegt daran, dass die Nummer ein Leerzeichen enthält.
Ich werde später darauf zurückkommen.
Wechsel zu Energy Question – IsNaN() ausprobieren
Jetzt habe ich Excel in Energy Question geladen.
Ich habe die Spalte als Textual content definiert und mit der Arbeit an dieser Herausforderung begonnen.
Der erste Versuch verwendet die Quantity.IsNaN() Funktion.
Diese Funktion gibt true zurück, wenn der Wert NaN ist. „NaN“ ist ein Platzhalter für nicht zutreffend, beispielsweise aufgrund einer Division durch 0.
Ich habe dies versucht, um festzustellen, ob ein Textual content NaN entspricht.
Dies ist der M-Code für die berechnete Spalte:
if Quantity.IsNaN((Worth)) = true
then (Worth)
else nullDas Ergebnis hat mich überrascht:
Das Ergebnis ist seltsamerweise, dass eine Zahl nicht in eine Zahl umgewandelt werden kann.
Ich nehme an, dass dies geschieht, weil der Datentyp der Spalte Textual content ist.
Dann habe ich versucht, die Spalte in eine Zahl umzuwandeln und die Funktion IsNaN() auf das Ergebnis anzuwenden:
if Quantity.IsNaN(Quantity.From((Worth))) = false
then Quantity.From((Worth))
else nullJetzt werden die Zahlen in Zahlen umgewandelt, aber die Textwerte führen zu einem Fehler:
Jetzt funktioniert die Logik für Zahlen.
Die Konvertierung schlägt jedoch für die Zeilen fehl, die Textual content enthalten. Dies führt zu Zeilen mit Fehlern.
Versuchen Sie Worth.Is() in Energy Question
Versuchen wir es mit einer anderen Funktion: Worth.Is()
Diese Funktion prüft, ob ein Wert mit einem Datentyp kompatibel ist.
Dies sollte der oben gezeigten Funktion ISNUMERIC() entsprechen:
if Worth.Is((Worth), Quantity.Sort) = true
then Quantity.From((Worth))
else nullLeider konnte auch diese Funktion nicht das erwartete Ergebnis zurückgeben:
Als ich den gleichen Ansatz wie oben ausprobierte, indem ich zuerst den Wert in eine Zahl umwandelte, erhielt ich das gleiche Ergebnis wie zuvor:
Daher vermute ich, dass die Funktion Worth.Is() einen Zahlendatentyp erwartet, aber das ergibt für mich keinen Sinn.
Zu diesem Zeitpunkt hatte ich keine Zeit für tiefergehende Recherchen, da mir die Zeit knapp wurde.
Es struggle an der Zeit, den Ansatz zu ändern.
Schaltkonzept
Jetzt habe ich untersucht, wie man Fehler in Energy Question abfängt.
Meine Idee struggle: Was wäre, wenn ich den Konvertierungsfehler erkennen und diese Informationen nutzen könnte?
Ich habe diese Seite mit nützlichen Informationen gefunden: Fehler – PowerQuery M | Microsoft Study
Daraus leitete ich diesen Ausdruck ab:
attempt Quantity.From((Worth)))Nachdem ich eine berechnete Spalte mit diesem Ausdruck hinzugefügt hatte, erhielt ich dieses Ergebnis:
Ich struggle optimistisch, da mir kein Fehler unterlaufen ist.
Als nächstes galt es, die Datensätze zu erweitern:
Ich brauchte die Fehlerspalten nicht, sondern nur die Wertspalte.
Dies ist das Ergebnis nach der Erweiterung:
Beachten Sie, dass ich die Spalten direkt in der Funktion ExpandRecordColumn() umbenannt habe.
Andernfalls hätte ich eine Spalte mit dem Namen (Wert.1) erhalten.
Dieses Ergebnis struggle das erste, bei dem ich keine Fehler erhielt.
Jetzt habe ich eine berechnete Spalte hinzugefügt, um zu überprüfen, ob die neue Spalte leer ist. Wenn ja, dann enthielt die ursprüngliche Wertspalte einen Textual content:
if (Numeric Worth) = null then (Worth) else nullHier das Ergebnis:
Nachdem ich die richtigen Datentypen festgelegt und die ursprüngliche Wertspalte entfernt hatte, erhielt ich diese Tabelle:
Behandeln Sie die Nummer mit Leerzeichen
Aber wir haben immer noch Zeile 17, die eine Zahl mit einem Leerzeichen enthielt.
Wie bin ich damit umgegangen?
Der einfachste Ansatz bestand darin, alle Leerzeichen aus der Spalte „Wert“ zu entfernen:
Aber ich musste diesen Schritt hinzufügen, bevor ich mit den Schritten zum Trennen der beiden Werttypen beginnen konnte:
Nach dem Hinzufügen dieses Schritts wird Zeile 17 als Zahl erkannt und korrekt gespeichert.
Hier sind die Daten nach dem Laden in Energy BI:
Dies funktionierte jedoch nur, wenn die Textwerte einzelne Wörter waren. Es funktionierte nicht, wenn dort Sätze oder mehrere Wörter gespeichert waren.
Abschluss
Dies struggle ein faszinierender Ausflug in die Funktionsweise von Energy Question oder der M-Sprache mit Datentypen.
Ich bin mir immer noch nicht sicher, was die Fehlerursachen sind.
Aber ich habe gelernt, wie man mit Fehlern umgeht oder wie man den Attempt-Aufruf verwendet und die Ausgabe verarbeitet.
Das struggle sehr hilfreich.
Wie Sie am Originalwert in Zeile 17 sehen können, ist die Datenqualität jedenfalls von größter Bedeutung.
Ich habe einen anderen Kunden, bei dem Benutzer aus verschiedenen Ländern an derselben Excel-Datei mit ihren eigenen Zahlenformaten arbeiten.
Das ist ein Albtraum, denn Excel ist gegenüber Datentypen äußerst tolerant. Es akzeptiert alles, auch wenn die Spalte als Zahl formatiert ist.
In dieser Scenario muss ich Benutzer dazu zwingen, die Formatierungsoptionen von Excel zu verwenden, um sicherzustellen, dass Zahlen konsistent als solche erkannt werden.
Ohne dies habe ich keine Likelihood, diese Daten ohne großen Aufwand zur Bereinigung der Zahlen in Energy BI zu importieren.
Und seien Sie versichert, dass Benutzer immer einen Weg finden, Zahlen in Excel durcheinander zu bringen.
Referenzen
Die Daten werden mit Zufallszahlen und Wörtern erstellt.
Hier ist die Referenz für die M-Sprache: Energy Question M-Formelsprachenreferenz – PowerQuery M | Microsoft Study