// ---------------------------------------------------------------------------
// Power-Query-Abfrage zur Arbeitsmappe Produkt_Marktplatz_Kalkulator.xlsx
//
// Sie macht dieselbe Bereinigung wie das Blatt 03_CLEAN_DATA, nur als
// wiederholbaren Ladeschritt: Bei neuen Rohdaten genuegt ein Klick auf
// "Aktualisieren", statt Formeln nach unten zu ziehen.
//
// EINBINDEN
//   1. Diese Datei oeffnen, den gesamten Inhalt kopieren.
//   2. In Excel:  Daten > Daten abrufen > Aus anderen Quellen > Leere Abfrage
//   3. Start > Erweiterter Editor, vorhandenen Text ersetzen, einfuegen.
//   4. Abfrage "RohDatenBereinigt" nennen, Schliessen und Laden nach.
//
// Warum liegt das hier und nicht in der Mappe: Die Mappe wird von einem
// Python-Skript erzeugt. Power Query wird von Excel in einem eigenen,
// binaeren Abschnitt der Datei gespeichert (DataMashup), den keine offene
// Bibliothek schreiben kann. Statt die Abfrage vorzutaeuschen, liegt sie hier
// vollstaendig und zum Einfuegen bereit.
//
// Voraussetzung: Die Tabelle auf Blatt 02_RAW_DATA heisst "RohDaten".
// ---------------------------------------------------------------------------
let
    // 1  Quelle: die benannte Tabelle auf Blatt 02_RAW_DATA
    Quelle = Excel.CurrentWorkbook(){[Name="RohDaten"]}[Content],

    // 2  Alles zunaechst als Text einlesen. Wer hier schon Typen erzwingt,
    //    verliert die fehlerhaften Zellen, bevor er sie gesehen hat.
    AlsText = Table.TransformColumnTypes(
        Quelle,
        {
            {"SKU", type text}, {"Bezeichnung", type text},
            {"Warengruppe (roh)", type text}, {"Marktplatz", type text},
            {"EK (roh)", type text}, {"VK brutto (roh)", type text},
            {"Gewicht (roh)", type text}, {"Bestand (roh)", type text}
        }
    ),

    // 3  Rundherum Leerzeichen weg, Grossschreibung vereinheitlichen.
    //    "  Filter", "filter" und "FILTER" sind danach dasselbe.
    Getrimmt = Table.TransformColumns(
        AlsText,
        {
            {"SKU", each Text.Trim(_ ?? ""), type text},
            {"Warengruppe (roh)", each Text.Proper(Text.Trim(_ ?? "")), type text},
            {"Marktplatz", each Text.Trim(_ ?? ""), type text}
        }
    ),

    // 4  Zuordnungstabelle der Warengruppen aus 04_PARAMETER dazuholen.
    //    Die Zuordnung steht nicht im Code, damit sie ohne Eingriff in die
    //    Abfrage gepflegt werden kann.
    Zuordnung = Table.TransformColumns(
        Excel.CurrentWorkbook(){[Name="Warengruppen"]}[Content],
        {{"Schreibweise (roh)", each Text.Proper(Text.Trim(_ ?? "")), type text}}
    ),
    Verbunden = Table.NestedJoin(
        Getrimmt, {"Warengruppe (roh)"},
        Zuordnung, {"Schreibweise (roh)"},
        "Treffer", JoinKind.LeftOuter
    ),
    Warengruppe = Table.AddColumn(
        Verbunden, "Warengruppe",
        each try Table.First([Treffer])[Warengruppe] otherwise null, type text
    ),

    // 5  Zahlen aus Text gewinnen - mit deutschem Dezimalkomma. Was sich nicht
    //    wandeln laesst, wird null und nicht etwa 0. Der Unterschied zwischen
    //    "kein Wert" und "Wert null" ist der ganze Punkt der Uebung.
    AlsZahl = (t as nullable text) as nullable number =>
        if t = null or Text.Trim(t) = "" then null
        else try Number.FromText(Text.Replace(Text.Trim(t), ",", ".")) otherwise null,

    Zahlen = Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(Warengruppe, "EK", each AlsZahl([#"EK (roh)"]), type nullable number),
                "VK brutto", each AlsZahl([#"VK brutto (roh)"]), type nullable number),
            "Gewicht kg", each AlsZahl([#"Gewicht (roh)"]), type nullable number),
        "Bestand", each AlsZahl([#"Bestand (roh)"]), type nullable number
    ),

    // 6  Doppelte Artikelnummern zaehlen, aber nicht loeschen. Welcher der
    //    beiden Datensaetze der richtige ist, entscheidet die Abfrage nicht.
    Gruppiert = Table.Group(Zahlen, {"SKU"}, {{"Anzahl", each Table.RowCount(_), Int64.Type}}),
    MitAnzahl = Table.ExpandTableColumn(
        Table.NestedJoin(Zahlen, {"SKU"}, Gruppiert, {"SKU"}, "z", JoinKind.LeftOuter),
        "z", {"Anzahl"}, {"AnzahlSKU"}
    ),

    // 7  Befund je Zeile. Dieselben sieben Regeln wie in 03_CLEAN_DATA.
    Befund = Table.AddColumn(MitAnzahl, "Befund", each
        Text.Combine(
            List.RemoveNulls({
                if [Warengruppe] = null then "Warengruppe unbekannt" else null,
                if [EK] = null then "EK fehlt" else null,
                if [VK brutto] = null then "VK fehlt" else null,
                if [Gewicht kg] = null then "Gewicht fehlt" else null,
                if [EK] <> null and [VK brutto] <> null and [VK brutto] <= [EK]
                    then "VK nicht ueber EK" else null,
                if [Bestand] <> null and [Bestand] < 0 then "Bestand negativ" else null,
                if [AnzahlSKU] > 1 then "SKU doppelt" else null
            }),
            ", "
        ), type text),

    // 8  Nur die Spalten behalten, die weiterverwendet werden.
    Ergebnis = Table.SelectColumns(
        Befund,
        {"SKU", "Bezeichnung", "Warengruppe", "Marktplatz",
         "EK", "VK brutto", "Gewicht kg", "Bestand", "Befund"}
    )
in
    Ergebnis
