Daten mit Umlauten in Power Query wie in Excel sortieren

[knobbi38]

@ws-53

  

[Andreas Killer]

Mit der Referenz ding... hat mir Copilot folgenden Code erstellt, der auch das gewünschte Ergebnis bringt.

Ich denke das ist von Interesse:

Ich habe mir den Code der function TableComparerSort von Camwallace genauer angeschaut, etwas recherchiert und siehe da man kann das auch mit einer einzigen Zeile machen.

Code:

= Table.Sort(Source, (x,y) => Comparer.FromCulture("de-DE", false)(x[Land], y[Land]))

Seine Function ist im Prinzip ein Wrapper der letztendlich diesen Code ausführt.

Da kommt eine Frage in meinen Kopf: Wie schreibt man einen Custom-Comparer der nach DIN 5007-2 sortiert?  

Andreas.

[knobbi38]

Hallo Andreas,

kleines Gedankenmodell:
in der Comparer Funktion werden vor der Sortierung in den zu vergleichenden Strings die Zeichen ä -> ae temporär umgewandelt und diese temporären Strings dann mit Comparer.FromCulture() ausgewertet.

Knobbi38


https://de.wikipedia.org/wiki/Alphabetische_Sortierung

[Andreas Killer]

in der Comparer Funktion werden vor der Sortierung in den zu vergleichenden Strings die Zeichen ä -> ae temporär umgewandelt und diese temporären Strings dann mit Comparer.FromCulture() ausgewertet.

Knobbi38

https://de.wikipedia.org/wiki/Alphabetische_Sortierung

So einfach ist das nicht.

a) Comparer.FromCulture kann nicht verwendet werden, der sortiert IMMER nach DIN 5007-1.

Es gibt ein weiteres interessantes Verhalten. Stelle ich die Sortierung in Windows auf Telefonbuch um, dann sortiert Excel nach DIN 5007-2. Power Query sortiert mit  Comparer.FromCulture("de-DE") IMMER nach DIN 5007-1.

Und auch wenn man beides gleich einstellt, Excel sortiert leere Zellen nach unten, Power Query nach oben.

b) Zitat aus dem Wiki:

Personennamen werden in Deutschland häufig (z. B. in Telefonbüchern) in der folgenden Art und Weise alphabetisch sortiert:

• Zuerst werden die Einträge nach Nachnamen sortiert, wobei akademische Grade wie „Prof.“, „Dr.“ und Namenszusätze wie „von“, „vor“, „am“, „zum“ weggelassen werden. Dabei ist zu beachten, dass Namenszusätze auch aus mehreren Wörtern bestehen können, wie etwa bei „von der Lippe“.
  
• Bei identischen Nachnamen wird anschließend nach eventuell vorhandenen Namenszusätzen alphabetisch sortiert, wobei Personennamen ohne Namenszusätze immer zuerst aufgeführt werden.
  
• Stimmen auch die Namenszusätze überein (oder sind keine vorhanden), wird als letztes anhand des Vornamens alphabetisch sortiert.
  

Diese Art der Sortierung ist in den bibliographischen Ordnungsregeln DIN 31638 geregelt. 

Mit ChatGPT und dgl. habe ich das hier zustande gebracht:

Code:

let func = (optional ignoreCase as nullable logical) as function =>
  let
    baseComparer = if ignoreCase = null then Comparer.Ordinal else if ignoreCase then Comparer.OrdinalIgnoreCase else Comparer.Ordinal,
    Normalize = (txt as nullable text) as nullable text =>
      let
        t0 = if txt = null then "" else txt,
        t1 = Text.Replace(t0, "ä", "ae"),
        t2 = Text.Replace(t1, "ö", "oe"),
        t3 = Text.Replace(t2, "ü", "ue"),
        t4 = Text.Replace(t3, "ß", "ss"),
        t5 = Text.Replace(t4, "Ä", "Ae"),
        t6 = Text.Replace(t5, "Ö", "Oe"),
        t7 = Text.Replace(t6, "Ü", "Ue")
      in
        t7,
    comparer = (x as nullable text, y as nullable text) as number =>
      let
        nx = Normalize(x),
        ny = Normalize(y)
      in
        baseComparer(nx, ny)
  in
    comparer,
documentation = [
  Documentation.Name =  "ComparerDIN5007_2",
  Documentation.Description = "Comparer to sort as desribed in DIN 5007-2",
  Documentation.LongDescription = Documentation.Description,
  Documentation.Category = "Table",
  Documentation.Source = "andreas.killer@gmx.net",
  Documentation.Version = "1.0",
  Documentation.Author = "Andreas Killer",
  Documentation.Examples = {[
    Description = "",
    Code = "
= Table.Sort(Source, (x, y) => ComparerDIN5007_2(false)(x[Land], y[Land]))
or
= TableComparerSort(Source,{{""Land"", Order.Ascending, ComparerDIN5007_2(false)}})

ignoreCase: To ignore the spelling or not.
The default value for ignoreCase is false.
",
    Result = ""]}]
in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Naja, aber mit den Namenszusätzen udgl. nach  DIN 31638 wie das Wiki sagt... da habe ich keine Idee wie man das geschickt löst. 

Am besten wäre ein Trick um die Vergleichsroutine aus dem System zu nutzen...

Andreas.

---

Nachtrag:

Sollte das Wiki korrekt sein, dann funktioniert ein einfaches ersetzen von ö zu oe nicht.

Wiki:
Göbel
Goethe
Göthe
Götz
Goldmann

PQ:
Göbel
Göthe
Goethe
Götz
Goldmann

Göthe und Goethe sind beim einfachen Ersetzen gleichberechtigt, daher kann PQ nicht zwischen beiden unterscheiden.
Der Name mit "oe" kommt jedoch vor "ö".

Also braucht es noch eine zusätzliche Sortierung...

[Andreas Killer]

Also braucht es noch eine zusätzliche Sortierung...

Selbst die Sortierung in Excel, wenn Windows auf Telefonbuch steht, ist nicht stabil. Hier werden Göthe und Goethe per Zufall sortiert. Aber wir können uns sicher sein das dies in keinen Telefonbuch so vorkommt.

Eine kleine Anpassung liefert für diesen Part eine stabile Sortierung:

Code:

let func = (optional ignoreCase as nullable logical) as function =>
  let
    baseComparer = if ignoreCase = null then Comparer.Ordinal else if ignoreCase then Comparer.OrdinalIgnoreCase else Comparer.Ordinal,
    Normalize = (txt as nullable text) as nullable text =>
      let
        t0 = if txt = null then "" else txt,
        t1 = Text.Replace(t0, "ä", "ae"),
        t2 = Text.Replace(t1, "ö", "oe"),
        t3 = Text.Replace(t2, "ü", "ue"),
        t4 = Text.Replace(t3, "ß", "ss"),
        t5 = Text.Replace(t4, "Ä", "Ae"),
        t6 = Text.Replace(t5, "Ö", "Oe"),
        t7 = Text.Replace(t6, "Ü", "Ue")
      in
        t7,
    comparer = (x as nullable text, y as nullable text) as number =>
      let
        nx = Normalize(x),
        ny = Normalize(y),
        res = baseComparer(nx, ny)
      in
        if res=0 then baseComparer(x, y) else res
  in
    comparer,
documentation = [
  Documentation.Name =  "ComparerDIN5007_2",
  Documentation.Description = "Comparer to sort as desribed in DIN 5007-2",
  Documentation.LongDescription = Documentation.Description,
  Documentation.Category = "Table",
  Documentation.Source = "andreas.killer@gmx.net",
  Documentation.Version = "1.01",
  Documentation.Author = "Andreas Killer",
  Documentation.Examples = {[
    Description = "",
    Code = "
= Table.Sort(Source, (x, y) => ComparerDIN5007_2(false)(x[Land], y[Land]))
or
= TableComparerSort(Source,{{""Land"", Order.Ascending, ComparerDIN5007_2(false)}})

ignoreCase: To ignore the spelling or not.
The default value for ignoreCase is false.
",
    Result = ""]}]
in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Für die Nameszusätze habe ich immer noch keine Idee...

Andreas.

[BoskoBiati]

Hi,

ich bin leider in PQ ein Anfänger, aber das Sortieren habe ich mal so hingekriegt:


  2025-12-05-steuerliche-behandlung-reisekosten-2026_Test.xlsx (Größe: 34,98 KB / Downloads: 6)

[Andreas Killer]

ich bin leider in PQ ein Anfänger, aber das Sortieren habe ich mal so hingekriegt:

Klappt nicht, aber trotzdem Danke für's mitdenken.

Der Haken ist das die Liste der Länder nur eine eindeutige Liste ist, wenn wir mal ein paar Namen nehmen dann sieht die Welt anders aus.

  Part of TableComparerSort.xlsx (Größe: 25,22 KB / Downloads: 1)

Die orangenen Tabellen sind die Quelle, die Sortierungen sind so korrekt... wobei ich mir nicht sicher bin ob nach DIN5007-1 bei einer Namensliste die Namenszusätze berücksichtigt werden müssten. Excel sortiert das so, das nehme ich erst mal als gegeben hin.

Ich habe leider keine DIN5007 als Klartext und da ich das im Moment eher "aus Sport" und nicht mit einem aktuellen Auftrag mache, kaufe ich mir auch keine. Aber wenn jemand da was weiß oder findet bin ich für jede Anregung dankbar.

Nun ja, die Listen enthalten ganz viele "Göthe" in div. Schreibweisen, ich lese nun die Tabelle ein und sortiere sie nach den Zufallszahlen, mache dann ein Table.Buffer drum damit Power Query das nicht intern wegoptimiert und den nächsten Schritt direkt ausführt. Dann sortiere ich mit meinem eigenen Code.

Das Problem beim Ersetzen von ö zu oe und dem anschließenden normalen Sortieren ist das Du nicht sicherstellen kannst das alle oe vor ö kommen, die sind ja jetzt gleich und nicht mehr unterscheidbar. Und so stellt mal schnell fest das Göthe und Goethe durcheinander sortiert werden; ist ja auch logisch. Aber in einem Telefonbuch stehen die nie und nimmer durcheinander.

Der Vorteil in meinem Code diesem Problem zu begegnen ist das ich ja immer nur 2 Elemente vergleichen muss, die Function comparer gibt eine Zahl zurück, 1 wenn der Wert x größer y ist, -1 wenn der Wert x kleiner y ist und 0 wenn der Wert gleich ist. Und dann gibt es ja auch noch den Fall das es in der Liste null (eine leere Zelle gibt). Funktioniert wie in VBA.StrComp.

Wenn ich als Ergebnis eine 0 bekomme, also Goethe und Göthe verglichen habe, dann mache ich den Vergleich nochmal aber mit dem Original-Namen (also ohne ö zu oe zu ersetzen). So kriege ich alle Goethe vor alle Göthe.

Sortieren ist wirklich schwierig, auch mit VBA sind diese Sortierungen eine echte Herausforderung. Nun ja, soweit wie es jetzt ist funktioniert es schon mal gut, zumindest sehe ich keine Fehler, aber die eigenen sieht man eh nicht.

Excel berücksichtigt selber beim Sortieren auch keine "von" im Namen, auch dann nicht wenn man die Sortierung in Windows umstellt... von daher habe ich schon mal einen brauchbaren Zwischenstand.

Andreas.

[knobbi38]

Hallo Andreas,

ein Vergleich von "Goethe" mit "Göthe" sollte eigentlich nicht vorkommen bzw. möglich sein, denn vor einem Vergleich werden doch in den zu vergleichenden Strings alle "ö" durch "oe" ersetzt.  

Für den Fall, das im Original tatsächlich eine Kombination "oe" vorkommt, warum auch immer, kann noch eine Prüfung des Originals auf das Vorhandensein eines "ö" gemacht werden, welches dann ggf. diesen String anders gewichtet.

Knobbi38

Links:
Praktische Anleitung und Varianten nach DIN 5007
0500-ABC-Standards Nach DIN 5007-2

[Andreas Killer]

Moin zusammen.

Nachdem ich damit nun viele Stunden damit verbracht habe um rauszubekommen wie die Sortierung gemacht wird und etlichen Code geschrieben habe... kann ich nun das meiste in die Mülltonne tun. Der Schlüssel ist hier:
https://learn.microsoft.com/de-de/window...dentifiers

Der Comparer für DIN5007-1 ist dieser, das hatten wir schon:
Comparer.FromCulture("de-DE", false)

Der Comparer für DIN5007-2 ist dieser:
Comparer.FromCulture("de-DE_phoneb", false)

Bleibt noch das Problem der Titel und Namenszusätze in Namen, da habe ich mir einen Splitter geschrieben der mir diese Arbeit abnimmt. Man dupliziert die Name-Spalte und dann trennt man diese via Splitter in Name/Titel/Zusatz und kann dann nach diesen Spalten mit jedem beliebigen Comparer beliebig sortieren.

Wenn man weiß wie es geht ist die Lösung immer ganz einfach.  

Andreas.

[knobbi38]

Hallo Andreas,

diese Konstante  "de-DE_phoneb" hatte ich auch schon in Verbindung mit C# gesehen, aber in PQ wurde diese Konstante nicht bei den unterstützten regionalen Erw. aufgeführt, sonst hätte ich das schon vorher gepostet. 

Wenn es dennoch funktioniert, alles Bestens und gut zu Wissen. Danke für die Rückmeldung.

Knobbi38