Proč Power BI hlásí duplicitní hodnotu, i když není

tl;dr: Power Query rozlišuje velikost písmen. DAX a model ve výchozím stavu ne. Tenhle mismatch umí rozhodit relationship klíče. Nejlepší postup: nepoužívat textové sloupce jako relationship key. Když musíš, sjednoť text v Power Query pomocí Text.Lower nebo Text.Upper, případně deduplikuj s case-insensitive comparerem:  Table.Distinct(Source, {"letter"}, Comparer.OrdinalIgnoreCase).

Neočekáváná duplicitní hodnota

Sloupec má duplicitní hodnoty na straně „One“ v relationshipu

Určitě jsi někdy viděl tento error:

Column 'letter' in Table 'dim_letter' contains a duplicate value 'a' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

A vsadím se, že jsi předtím v Power Query použil Remove Duplicates, abys smazal duplicitní hodnoty, než jsi načetl tabulku. Na první pohled jsi udělal vše správně, akorát že vůbec.

První past: textový sloupec jako relationship key, není to uplně best practice, ale dnes to přežiju.

Druhá past byla, že jsi věřil, že Power Query ti smaže duplicity, což se stalo, akorát jinak než jsi pravděpodobně očekával.

Case-sensitivity nesoulad

Je pain používat "rozlišování velikosti písmen", takže ponechám case-sensitive a case-insensitive.

Power Query, neboli M, je case-sensitive. Když v textovém sloupci použiješ Remove Duplicates, hodnoty lišící se jen velikostí písmen bere jako různé. „a“ a „A“ jsou dvě distinct values, obě zůstanou.

DAX se chová jinak. Při načítání tabulky VertiPaq staví dictionary z textového sloupce case-insensitively a nechá první nalezený výskyt. Když narazí nejdřív na „a“, všechny další „A“ i „a“ se namapují na „a“.

Stejné i pro delší řetězce. Když se jako první objeví „gOOD morning“ a zbytek je „Good morning“, uvidíš „gOOD morning“ všude.

Rozdíl mezi M and DAX při rozlišování písmen

Odstraň duplicity v Power Query

Když v Power Query použiješ Remove Duplicates, nesmažeš hodnoty, které mají stejná písmena ve stejném pořadí a liší se jen velikostí písmen (fancy věta pro „slovo“):

Mazaání duplicit v M

Tohle je v pořádku, protože Mko odstranilo všechny odlišné hodnoty ze sloupečku (v tomto případě celé tabulky, jelikož máme jen jeden sloupec). V Column distribution, vidíš 4 distinct a 4 unique hodnoty.

Po načtení, dostaneme 4 řádky, ale jen 3 distinct hodnoty. Kdyby na tomto sloupci byl postavený relationship (na straně One), vrátí to error a spadne refresh semantického modelu.

Distribuce v DAXu

Jak to vyřešit

Mám tři rychlé fixy.

První: nepoužívej textový sloupec jako relationship key. Easy win.

Druhý: normalizuj text pomocí Text.Upper nebo Text.Lower před načtením a pak dej Remove Duplicates. Buď přidej nový sloupec, abys částečně zachoval originál pro mapping, nebo transformuj stávající přes Table.TransformColumns.

let
    Source = src,
    addTextLower = Table.AddColumn(Source, "letter_lowered", each Text.Lower([letter])),
    removeDuplicates = Table.Distinct(addTextLower, {"letter_lowered"})
in
    removeDuplicates

nebo

let
    Source = src,
    addTextLower = Table.TransformColumns(Source, {{"letter", each Text.Lower(_), type text}}),
    removeDuplicates = Table.Distinct(addTextLower, {"letter"})
in
    removeDuplicates

Třetí: použij Table.Distinct s case-insensitive comparerem viz níže.

let    
    Source = src,
    removeDuplicates = Table.Distinct(Source, {"letter", Comparer.OrdinalIgnoreCase})
in
    removeDuplicates

Case-insensitivity validace v Power Query

Mrkneme, jak šlamastiku objevit už přímo v Power Query, abys s tím mohl něco udělat. Vestavěné Keep Duplicates je case-sensitive a kód, který z něj vypadne, je pro většinu z nás na úpravy dost náročný. Existuje mnohem jednodušší způsob, jak zkontrolovat, jestli máš duplicity.

Pro rychlý check klikni na sloupec, který chceš testnout, a dej Remove Duplicates. Pak ve vygenerovaném kroku Table.Distinct přidej jednu equationCriteria: Comparer.OrdinalIgnoreCase.

= Table.Distinct(Source, {"letter", Comparer.OrdinalIgnoreCase} )

Tenhle comparer přepne defaultní case-sensitive kontrolu na case-insensitive, pak odstraní každý další výskyt po prvním matchi a zachová casing první nalezené hodnoty.

Duplicitní záznam odstraněn case-insensitively

Pokud chceš hned vidět, kolik ti deduplikátor smazal řádků, napiš:

rowsRemoved = Table.RowCount(Source) - Table.RowCount(removeDuplicates)

Kde Source je tvůj originál krok, a removeDuplicates je krok s Table.Distinct funkcí.

Nastav tlačítko Keep Duplicates jako case-insensitive

Pokud ti nevadí se trošku hrabat ve vygenerovaném kódu, anebo ti nevadí si napsat svůj vlastní, dá se vestavěné Keep Duplicates přepnout na case-insensitive. Jenom je potřeba přidělat či poupravit dvě drobnosti.

Tady je původní kód v hezčím formátu, který Power Query vyplivne po kliknutí na Keep Rows -> Keep Duplicates:

Keep Duplicates

= let
    columnNames = {"letter"},
    addCount = Table.Group(
        Source,
        columnNames,
        {{"Count", Table.RowCount, type number}}
    ),
    selectDuplicates = Table.SelectRows(
        addCount,
        each [Count] > 1
    ),
    removeCount = Table.RemoveColumns(
        selectDuplicates,
        "Count"
    )
in
    Table.Join(
        Source,
        columnNames,
        removeCount,
        columnNames,
        JoinKind.Inner
    )

Způsob, jakým je originální kód napsaný, nám bohužel neumožní si nechat všechny hodnoty skrz case-insensitive porovnání. Table.Group sám o sobě vždycky fyzicky groupuje slopečky jako case-sensitive.

První úpravou bude přidání nového setu tabulek. Pro každý řádek přidáme jednu novou. Toto potom bude výsledná tabulka. Dělám to tímto způsobem, jelikož i při zaplém case-insensitive módu, fyzicky přijdeme o case-insensitive hodnoty v originální tabulce.

Druhá úprava je potom vlastní agregátory nastavit jako case-insensitive, toto uděláme zase stejnou comparer funkcí: Comparer.OrdinalIgnoreCase.

Tady celý ready-to-copy (uprav columnNames a previousStep):

= let
        previousStep = Source,
        columnNames = {"letter"},
        addCount = Table.Group(
            previousStep, 
            columnNames, 
            {
                {"tbl", each _, type table}, 
                {"count", Table.RowCount, type number}
            }, 
            GroupKind.Global, 
            Comparer.OrdinalIgnoreCase
        ),
        selectDuplicates = Table.SelectRows(
            addCount, 
            each [count] > 1
        ),
        result = Table.Combine(selectDuplicates[tbl])
    in
        result

Skoro stejné jako originál. Jediná větší změna je extra agregační sloupec tbl, který se pak vrací jako výsledek:

{"tbl", each _, type table}, 

Dál jsou nasazené volitelné parametry, aby se porovnávalo case-insensitively:

        GroupKind.Global, 
        Comparer.OrdinalIgnoreCase

Přidal jsem i proměnnou previousStep ať jde ladit na jednom místě místo editace přímo v Table.Group:

previousStep = Source,

Zbytek je víceméně stejný. Odstranil jsem část se sloupcem Count, protože ho nemusíme odstraňovat, jelikož potom vrácíme tbl napřímo. Navíc jsem přidal proměnnou pro výsledek, abych do in psal název proměnnou a ne výraz. Toto je primárně pro debugging a osobní preference. Klidně pácni výraz s Table.Combine napřímo do in.

Case-insensitive výsledek