Powerquery - Text ersetzen und Dauer

[Fröschl]

Ich importiere mit Hilfe von PQ meine wöchentliche Telefonliste. Diese möchte ich natürlich auch auswerten.

Wie kann ich die Telefonnummern alle gleich formatieren? 95% der Nummern sind im Format +4912398765 einige wenige aber im Format 012398765. Wie kann ich nun in PQ wenn eine "0" am Anfang der Telefonnummer steht, diese durch "+49" ersetzen lassen?

Ich habe außerdem eine Spalte mit dem Wert Dauer in der Form "00:12:21". Ich möchte alle Telefonnummern gruppieren, die Anzahl und die Gesamtdauer ausgeben lassen. Das Zählen der Anrufe klappt ohne Probleme, wenn die die Dauer addiere gibt es ab zwei Anrufe einen Error! Wo muss ich hier drehen?

Excel 365, macOS

Danke!

O.

[Warkings]

Zur ersten Frage: 

Code:

if Text.Start([TelNr],1)="0" then "+49" & Text.Middle([TelNr],1,Text.Length([TelNr])-1) else [TelNr]

Zur zweiten kann man ohne Beispieldaten, wo der Fehler (welcher überhaupt, das wird nicht spezifiziert ) auftritt, nur raten.

PS Du hast einen Mac, richtig? Wusste nicht, dass dort PQ verfügbar ist.
Aber ist wohl so  Importieren und Strukturieren von Daten in Excel für Mac (Power Query) - Microsoft-Support

[Fröschl]

Als Beispiel:

Telefon Dauer
+49123 00:00:01
+49123 00:05:05
+49890 00:00:01
+49890 00:05:05
+49090 00:00:01
+49123 01:05:05

Soll ergeben:

Telefon Gesamtdauer Anzahl
+49123 01:10:11 3
+49090 00:00:01 1
+49890 00:05:06 2

Ich hoffe jetzt ist es verständlicher

[Warkings]

Und wie lautet der Fehler?

[shift-del]

Zur zweiten kann man ohne Beispieldaten, wo der Fehler (welcher überhaupt, das wird nicht spezifiziert ) auftritt, nur raten.

Das ist der berühmte unspezifizierte Fehler.

[Warkings]

Mein Ergebnis
   

ChatGPT schlägt folgendes vor

1. Formatierung der Telefonnummern:
Du kannst die Funktion "Ersetzen" in PQ verwenden, um die "0" am Anfang der Telefonnummern durch "+49" zu ersetzen. Hier ist ein Beispiel-Code:

Code:

= Table.TransformColumns(
    DeineQuelltabelle,
    {
        {"Telefonnummer", each if Text.StartsWith(_, "0") then "+49" & Text.From(Number.RoundDown(Number.FromText(_))) else _}
    }
)

Dieser Code prüft, ob die Telefonnummer mit "0" beginnt. Wenn ja, wird die "0" durch "+49" ersetzt.

Funktioniert auch (natürlich nicht via Copy & Paste !!!)

Und 

2. Gruppierung und Summierung der Dauer:
Du kannst die Funktion Group By in PQ verwenden, um die Telefonnummern zu gruppieren und dann die Summe der Dauer zu berechnen. Hier ist ein Beispiel-Code:

Code:

= DeineQuelltabelle
  |> Group By("Telefonnummer", {"Anzahl", each Table.RowCount(_), Int64.Type}, {"Gesamtdauer", each List.Sum(Duration.FromText(_[Dauer]))})

Dieser Code gruppiert die Tabelle nach der Spalte "Telefonnummer" und berechnet die Anzahl der Anrufe sowie die Gesamtdauer

Dürfte auch funktionieren.

Auf jeden Fall gibt ChatGPT hier die richtigen Hinweise und mit etwas Sachverstand sollte man zum Ergebnis kommen.

[Fröschl]

Der Fehler lautet:

   

[Warkings]

Schau Dir den Begriff Datentyp wie Duration an, bereite in Deinem uns unbekannten M-Code die Spalten so vor, dass Du sie für die gewünschten Operationen nutzen kannst.
Duration functions - PowerQuery M | Microsoft Learn

Noch ein Hinweis von ChatGPT

Entschuldige den Fehler. Um die Dauer korrekt zu summieren, müssen die Dauerwerte zuerst in Minuten umgewandelt werden. Hier ist eine aktualisierte Version des Codes:

Code:

= DeineQuelltabelle
  |> Group By("Telefonnummer",
              {"Anzahl", each Table.RowCount(_), Int64.Type},
              {"Gesamtdauer", each Duration.FromText(_[Dauer]) / #duration(0, 0, 1, 0)})

In diesem Code wird die Duration.FromText-Funktion auf die Spalte "Dauer" angewendet, und das Ergebnis wird durch #duration(0, 0, 1, 0) geteilt, um die Dauer in Minuten umzuwandeln. Dadurch kannst du die Dauerwerte erfolgreich summieren.

Geht zwar in die falsche Richtung, aber mit etwas NI (=natürlicher Intelligenz) sollte man den richtigen Weg finden.

[LCohen]

=GLÄTTEN(WECHSELN(" "&A1;" 0";"+49"))

[Fröschl]

Mein Ergebnis


ChatGPT schlägt folgendes vor

Funktioniert auch (natürlich nicht via Copy & Paste !!!)

Und 
Dürfte auch funktionieren.

Auf jeden Fall gibt ChatGPT hier die richtigen Hinweise und mit etwas Sachverstand sollte man zum Ergebnis kommen.

Und wo gebe ich das ein?