Hoofdlettergebruik in Power Query M

Het werken met tekstgegevens kan soms rommelig zijn. De naam van de stad Redmond kan bijvoorbeeld worden weergegeven in een database met verschillende hoofdletters ('Redmond', 'redmond' en 'REDMOND'). Dit kan een probleem veroorzaken bij het transformeren van de gegevens in Power Query, omdat de Power Query M-formuletaal hoofdlettergevoelig is.

Gelukkig biedt Power Query M functies voor het opschonen en normaliseren van het hoofdlettergebruik van tekstgegevens. Er zijn functies om tekst te converteren naar kleine letters (abc), hoofdletters (ABC) of de juiste hoofdletter (Abc). Daarnaast biedt Power Query M ook verschillende manieren om case helemaal te negeren.

In dit artikel leest u hoe u het hoofdlettergebruik van woorden in tekst, lijsten en tabellen kunt wijzigen. Er worden ook verschillende manieren beschreven om hoofdletters te negeren tijdens het bewerken van gegevens in tekst, lijsten en tabellen. Daarnaast wordt in dit artikel besproken hoe u kunt sorteren op basis van hoofdletters en kleine letters.

Hoofdlettergebruik in tekst wijzigen

Er zijn drie functies waarmee tekst wordt geconverteerd naar kleine letters, hoofdletters en beginkapitalen. De functies zijn Text.Lower, Text.Upperen Text.Proper. In de volgende eenvoudige voorbeelden ziet u hoe deze functies in tekst kunnen worden gebruikt.

Alle tekens in tekst converteren naar kleine letters

In het volgende voorbeeld ziet u hoe u alle tekens in een tekenreeks converteert naar kleine letters.

let
    Source = Text.Lower("The quick brown fox jumps over the lazy dog.")
in
    Source

Deze code produceert de volgende uitvoer:

the quick brown fox jumps over the lazy dog.

Alle tekens in tekst converteren naar hoofdletters

In het volgende voorbeeld ziet u hoe u alle tekens in een tekenreeks converteert naar hoofdletters.

let
    Source = Text.Upper("The quick brown fox jumps over the lazy dog.")
in
    Source

Deze code produceert de volgende uitvoer:

THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG.

Alle woorden converteren naar initiële hoofdletters

In het volgende voorbeeld ziet u hoe u alle woorden in de zin converteert naar hoofdlettergebruik.

let
    Source = Text.Proper("The quick brown fox jumps over the lazy dog.")
in
    Source

Deze code produceert de volgende uitvoer:

The Quick Brown Fox Jumps Over The Lazy Dog.

Hoofdlettergebruik in lijsten wijzigen

Bij het wijzigen van hoofdletters in lijsten is de meest voorkomende functie die moet worden gebruikt List.Transform. In de volgende eenvoudige voorbeelden ziet u hoe deze functie kan worden gebruikt in lijsten.

Alle items converteren naar kleine letters

In het volgende voorbeeld ziet u hoe u alle items in een lijst kunt wijzigen in kleine letters.

let
    Source = {"Squash", "Pumpkin", "ApPlE", "pear", "orange", "APPLE", "Pear", "pear"},
    #"Lower Case" = List.Transform(Source, Text.Lower)
in
    #"Lower Case"

Deze code produceert de volgende uitvoer:

Schermopname van de lijst die is aangemaakt door de lijsttransformatiefunctie die resultaten in kleine letters oplevert.

Alle items converteren naar hoofdletters

In het volgende voorbeeld ziet u hoe u alle items in een lijst kunt wijzigen in hoofdletters.

let
    Source = {"Squash", "Pumpkin", "ApPlE", "pear", "orange", "APPLE", "Pear", "pear"},
    #"Upper Case" = List.Transform(Source, Text.Upper)
in
    #"Upper Case"

Deze code produceert de volgende uitvoer:

Schermopname van de lijst die is gemaakt door de functie lijsttransformatie die hoofdletterresultaten produceert.

Alle items converteren naar het juiste hoofdlettergebruik

In de volgende exmaple ziet u hoe u alle items in een lijst kunt wijzigen in de juiste hoofdletters.

let
    Source = {"Squash", "Pumpkin", "ApPlE", "pear", "orange", "APPLE", "Pear", "pear"},
    #"Proper Case" = List.Transform(Source, Text.Proper)
in
    #"Proper Case"

Deze code produceert de volgende uitvoer:

Schermopname van de lijst die is gemaakt door de transformatiefunctie van de lijst die de juiste caseresultaten produceert.

Het veranderen van hoofdlettergebruik in tabellen

Bij het wijzigen van hoofdletters in tabellen is Table.TransformColumnsde meest voorkomende functie die moet worden gebruikt. Er is ook een functie die u kunt gebruiken om het hoofdlettergebruik van tekst in een rij te wijzigen.Table.TransformRows Deze functie wordt echter niet zo vaak gebruikt.

In de volgende eenvoudige voorbeelden wordt getoond hoe de Table.TransformColumns functie kan worden gebruikt om hoofdletters in tabellen te wijzigen.

Alle items in een tabelkolom converteren naar kleine letters

In het volgende voorbeeld ziet u hoe u alle items in een tabelkolom wijzigt in kleine letters, in dit geval de klantnamen.

let
    Source = #table(type table [CUSTOMER = text, FRUIT = text],
    {
        {"Tulga", "Squash"},
        {"suSanna", "Pumpkin"},
        {"LESLIE", "ApPlE"},
        {"Willis", "pear"},
        {"Dilbar", "orange"},
        {"ClaudiA", "APPLE"},
        {"afonso", "Pear"},
        {"SErgio", "pear"}
    }),
    #"Lower Case" = Table.TransformColumns(Source, {"CUSTOMER", Text.Lower})
in
    #"Lower Case"

Deze code produceert de volgende uitvoer:

Schermopname van de tabel die is gemaakt door de functie voor tabeltransformatie die de kolommen omzet en resultaten in kleine letters oplevert.

Alle items in een tabelkolom converteren naar hoofdletters

In het volgende voorbeeld ziet u hoe u alle items in een tabelkolom wijzigt in hoofdletters, in dit geval de namen van vruchten.

let
    Source = #table(type table [CUSTOMER = text, FRUIT = text],
    {
        {"Tulga", "Squash"},
        {"suSanna", "Pumpkin"},
        {"LESLIE", "ApPlE"},
        {"Willis", "pear"},
        {"Dilbar", "orange"},
        {"ClaudiA", "APPLE"},
        {"afonso", "Pear"},
        {"SErgio", "pear"}
    }),
    #"Upper Case" = Table.TransformColumns(Source, {"FRUIT", Text.Upper})
in
    #"Upper Case"

Deze code produceert de volgende uitvoer:

Schermopname van de tabel die is gemaakt door de functie voor het transformeren van tabelkolommen, waardoor resultaten in hoofdletters worden geproduceerd.

Alle items in een tabel omzetten naar het juiste hoofdlettergebruik

In het volgende voorbeeld ziet u hoe u alle items in beide tabelkolommen kunt wijzigen in de juiste hoofdletters.

let
    Source = #table(type table [CUSTOMER = text, FRUIT = text],
    {
        {"Tulga", "Squash"},
        {"suSanna", "Pumpkin"},
        {"LESLIE", "ApPlE"},
        {"Willis", "pear"},
        {"Dilbar", "orange"},
        {"ClaudiA", "APPLE"},
        {"afonso", "Pear"},
        {"SErgio", "pear"}
    }),
    #"Customer Case" = Table.TransformColumns(Source, {"CUSTOMER", Text.Proper}),
    #"Proper Case" = Table.TransformColumns(#"Customer Case", {"FRUIT", Text.Proper})
in
    #"Proper Case"

Deze code produceert de volgende uitvoer:

Schermopname van de tabel die is gemaakt door de functie voor tabelkolomtransformatie die het juiste hoofdlettergebruik in beide kolommen produceert.

Hoofdlettergebruik negeren

In veel gevallen moet u bij het zoeken of vervangen van items mogelijk het geval negeren van het item dat u zoekt. Omdat de Power Query M-formuletaal hoofdlettergevoelig is, worden items die identiek zijn maar in verschillende hoofdlettergebruik voorkomen, beschouwd als verschillend en niet als identiek. Een methode om lettertypegevoeligheid te negeren, omvat het gebruik van de Comparer.OrdinalIgnoreCase functie in functies die een equationCriteria parameter of een comparer parameter bevatten. Een andere methode voor het negeren van hoofdletters omvat het gebruik van de IgnoreCase optie (indien beschikbaar) in functies die een options parameter bevatten.

Hoofdlettergebruik in tekst negeren

Zoekopdrachten in tekst vereisen soms dat u hoofdlettergevoeligheid negeert om alle exemplaren van de gezochte tekst te kunnen vinden. Tekstfuncties gebruiken in het algemeen de Comparer.OrdinalIgnoreCase functie in de comparer parameter om hoofdlettergevoeligheid te negeren bij het testen op overeenstemming.

In het volgende voorbeeld ziet u hoe u hoofdletters kunt negeren bij het controleren of een zin een specifiek woord bevat, ongeacht het gebruik van hoofdletters.

let
    Source = Text.Contains(
        "The rain in spain falls mainly on the plain.",
        "Spain",
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

true

In het volgende voorbeeld ziet u hoe u de beginpositie van het laatste exemplaar van het woord 'the' in de zin ophaalt, ongeacht het geval.

let
    Source = Text.PositionOf(
        "THE RAIN IN SPAIN FALLS MAINLY ON THE PLAIN.",
        "the",
        Occurrence.Last,
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

34

Hoofdlettergebruik in lijsten negeren

Elke lijstfunctie die een optionele equationCriteria parameter bevat, kan de Comparer.OrdinalIgnoreCase functie gebruiken om hoofdletters in de lijst te negeren.

In het volgende voorbeeld wordt gecontroleerd of een lijst een specifiek item bevat, terwijl het geval wordt genegeerd. In dit voorbeeld List.Contains kunt u slechts één item in de lijst vergelijken. U kunt een lijst niet vergelijken met een lijst. Hiervoor moet u gebruiken List.ContainsAny.

let
    Source = List.Contains(
        {"Squash", "Pumpkin", "ApPlE", "pear", "orange", "APPLE", "Pear", "pear"},
        "apple",
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

true

In de volgende voorbeelden wordt gecontroleerd of een lijst alle opgegeven items in de tweede parameter (value) bevat, terwijl de case wordt genegeerd. Als een item niet in de lijst staat, zoals cucumber in het tweede voorbeeld, retourneert de functie FALSE.

let
    Source = List.ContainsAll(
        {"Squash", "Pumpkin", "ApPlE", "pear", "orange", "APPLE", "Pear", "pear"},
        {"apple", "pear", "squash", "pumpkin"},
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

true

let
    Source = List.ContainsAll(
        {"Squash", "Pumpkin", "ApPlE", "pear", "orange", "APPLE", "Pear", "pear"},
        {"apple", "pear", "squash", "pumpkin", "cucumber"},
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

false

In het volgende voorbeeld wordt gecontroleerd of een van de items in de lijst appels of peren is, terwijl het geval wordt genegeerd.

let
    Source = List.ContainsAny(
        {"Squash", "Pumpkin", "ApPlE", "PEAR", "orange", "APPLE", "Pear", "peaR"},
        {"apple","pear"},
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

true

In het volgende voorbeeld blijven alleen unieke items behouden, terwijl hoofdletters en kleine letters worden genegeerd.

let
    Source = List.Distinct(
        {"Squash", "Pumpkin", "ApPlE", "PEAR", "orange", "APPLE", "Pear", "peaR"},
        Comparer.OrdinalIgnoreCase
    )
in
    Source

Deze code produceert de volgende uitvoer:

Schermopname van de lijst die door de afzonderlijke lijstfunctie is gemaakt terwijl hoofdletters en kleine letters worden genegeerd.

In het vorige voorbeeld wordt in de uitvoer het geval weergegeven van het eerste unieke item dat in de lijst is gevonden. Hoewel er dus twee appels (ApPlE en APPLE) zijn, wordt alleen het eerste voorbeeld weergegeven.

In het volgende voorbeeld worden alleen unieke items bewaard met negeren van hoofdletterverschillen, maar worden ook alle resultaten in kleine letters weergegeven.

let
    Source = List.Distinct(
        {"Squash", "Pumpkin", "ApPlE", "PEAR", "orange", "APPLE", "Pear", "peaR"},
        Comparer.OrdinalIgnoreCase
    ),
    #"Lower Case" = List.Transform(Source, Text.Lower)
in
    #"Lower Case"

Deze code produceert de volgende uitvoer:

Schermopname van de lijst die door de lijst distinct functie is gemaakt, waarbij hoofdletters worden genegeerd, maar ook in kleine letters wordt uitgegeven.

Hoofdlettergebruik in tabellen negeren

Tabellen hebben verschillende manieren om hoofdlettergevoeligheid te negeren. Tabelfuncties zoals Table.Contains, Table.Distincten Table.PositionOf alle bevatten equationCriteria parameters. Deze parameters kunnen de Comparer.OrdinalIgnoreCase functie gebruiken om hoofdletters in tabellen te negeren, op ongeveer dezelfde manier als de lijsten in de vorige secties. Tabelfuncties, zoals Table.MatchesAnyRows die een condition parameter bevatten, kunnen ook Comparer.OrdinalIgnoreCase in andere tabelfuncties verpakken om hoofdletters en kleine letters te negeren. Andere tabelfuncties, met name voor fuzzy matching, kunnen de IgnoreCase optie gebruiken.

In het volgende voorbeeld ziet u hoe u specifieke rijen selecteert die het woord 'peer' bevatten, ongeacht hoofdlettergebruik. In dit voorbeeld wordt de condition parameter met Table.SelectRowsText.Contains als voorwaarde gebruikt om de vergelijkingen te maken terwijl case wordt genegeerd.

let
    Source = #table(type table[CUSTOMER = text, FRUIT = text],
    {
        {"Tulga", "Squash"},
        {"suSanna", "Pumpkin"},
        {"LESLIE", "ApPlE"},
        {"Willis", "pear"},
        {"Dilbar", "orange"},
        {"ClaudiA", "APPLE"},
        {"afonso", "Pear"},
        {"SErgio", "pear"}
    }),
    #"Select Rows" = Table.SelectRows(
        Source, each Text.Contains([FRUIT], "pear", Comparer.OrdinalIgnoreCase))
in
    #"Select Rows"

Deze code produceert de volgende uitvoer:

Schermopname van de tabel die is gemaakt door de functie

In het volgende voorbeeld ziet u hoe u kunt bepalen of een van de rijen in een tabel een pear in de FRUIT kolom bevat. In dit voorbeeld wordt Comparer.OrdinalIgnoreCase in een Text.Contains functie gebruikgemaakt van de condition parameter van de Table.MatchesAnyRows functie.

let
    Source = #table(type table [CUSTOMER = text, FRUIT = text],
    {
        {"Tulga", "Squash"},
        {"suSanna", "Pumpkin"},
        {"LESLIE", "ApPlE"},
        {"Willis", "PEAR"},
        {"Dilbar", "orange"},
        {"ClaudiA", "APPLE"},
        {"afonso", "Pear"},
        {"SErgio", "peAR"}
        }),
    #"Select Rows" = Table.MatchesAnyRows(Source,
        each Text.Contains([FRUIT], "pear", Comparer.OrdinalIgnoreCase))
in
    #"Select Rows"

Deze code produceert de volgende uitvoer:

true

In het volgende voorbeeld ziet u hoe u een tabel kunt maken met waarden die zijn ingevoerd door gebruikers die een kolom met hun favoriete vruchten bevatten, zonder ingestelde indeling. Eerst wordt deze kolom fuzzy gematcht om de namen van hun favoriete fruit te extraheren, die vervolgens in een aparte kolom met de naam Cluster worden weergegeven. Vervolgens wordt de clusterkolom onderzocht om de verschillende afzonderlijke vruchten in de kolom te bepalen. Zodra de unieke vruchten zijn bepaald, is een laatste stap het wijzigen van alle fruitnamen in kleine letters.

let
    // Load a table of user's favorite fruits into Source
    Source = #table(type table [Fruit = text], {{"blueberries"},
        {"Blue berries are simply the best"}, {"strawberries"}, {"Strawberries = <3"},
        {"Apples"}, {"'sples"}, {"4ppl3s"}, {"Bananas"}, {"fav fruit is bananas"},
        {"Banas"}, {"My favorite fruit, by far, is Apples. I simply love them!"}}
    ),
    // Create a Cluster column and fuzzy match the fruits into that column
    #"Cluster fuzzy match" = Table.AddFuzzyClusterColumn(
        Source, "Fruit", "Cluster",
        [IgnoreCase = true, IgnoreSpace = true, Threshold = 0.5]
    ),
    // Find the distinct fruits from the Cluster column
    #"Ignore cluster case" = Table.Distinct(
        Table.SelectColumns(#"Cluster fuzzy match", "Cluster"),
        Comparer.OrdinalIgnoreCase
    ),
    // Set all of the distinct fruit names to lower case
    #"Set lower case" = Table.TransformColumns(#"Ignore cluster case",
        {"Cluster", Text.Lower}
    )
in
    #"Set lower case"

Deze code produceert de volgende uitvoer:

Schermopname van de tabel die is gemaakt door fuzzy matching zonder op hoofdletters te letten.

Hoofdletters en sorteren

Lijsten en tabellen kunnen beide worden gesorteerd met respectievelijk List.Sort ofwel Table.Sort. Het sorteren van tekst is echter afhankelijk van het geval van de gekoppelde items in de lijst of tabel om de werkelijke sorteervolgorde te bepalen (oplopend of aflopend).

De meest voorkomende vorm van sorteren maakt gebruik van tekst die allemaal kleine letters, hoofdletters of hoofdletters zijn. Als er een combinatie van deze gevallen is, is de oplopende sorteervolgorde als volgt:

  1. Alle tekst in de lijst- of tabelkolom die begint met een hoofdletter, is eerst.
  2. Als er overeenkomende tekst is, maar de ene in een correct gebruik van hoofdletters staat en de andere geheel in hoofdletters is, komt de geheel in hoofdletters versie eerst.
  3. Kleine letters worden vervolgens gesorteerd.

Voor aflopende volgorde worden de eerder vermelde stappen omgekeerd verwerkt.

Het volgende voorbeeld bevat bijvoorbeeld een combinatie van alle kleine letters, hoofdletters en juiste hoofdletters die in oplopende volgorde moeten worden gesorteerd.

let
    Source = { "Alpha", "Beta", "Zulu", "ALPHA", "gamma", "alpha",
        "beta", "Gamma", "Sierra", "zulu", "GAMMA", "ZULU" },
    SortedList = List.Sort(Source, Order.Ascending)
in
    SortedList

Deze code produceert de volgende uitvoer:

Schermopname van de lijst die is gemaakt door tekst met kleine letters, hoofdletters en juiste hoofdletters te sorteren.

Hoewel dit niet gebruikelijk is, hebt u mogelijk een combinatie van hoofdletters en kleine letters in tekst om te sorteren. De oplopende sorteervolgorde in dit geval is:

  1. Alle tekst in de lijst- of tabelkolom die begint met een hoofdletter, is eerst.
  2. Als er overeenkomende tekst is, wordt de tekst met het maximum aantal hoofdletters links daarna verwerkt.
  3. Kleine letters worden vervolgens gesorteerd, waarbij het maximum aantal hoofdletters aan de rechterkant eerst wordt uitgevoerd.

In elk geval kan het handiger zijn om de tekst te converteren naar een consistent hoofdlettergebruik voordat u sorteert.

Normalisatie van Power BI Desktop

Power Query M is hoofdlettergevoelig en maakt onderscheid tussen verschillende hoofdletters van dezelfde tekst. Bijvoorbeeld worden 'Foo', 'foo' en 'FOO' als verschillend behandeld. Wanneer de gegevens echter in Power BI Desktop worden geladen, worden de tekstwaarden genormaliseerd, wat betekent dat Power BI Desktop ze behandelt als dezelfde waarde, ongeacht hun hoofdlettergebruik. Dus als u gegevens wilt transformeren terwijl u hoofdlettergevoeligheid in uw gegevens behoudt, moet u de gegevenstransformatie in Power Query afhandelen voordat u de gegevens in Power BI Desktop laadt.

In de volgende tabel in Power Query ziet u bijvoorbeeld verschillende gevallen in elke rij van de tabel.

Schermopname van een tabel met de tekst foo en ook met verschillende gevallen.

Wanneer deze tabel in Power BI Desktop wordt geladen, worden de tekstwaarden genormaliseerd, wat resulteert in de volgende tabel.

Schermopname van dezelfde tabel die in Power BI Desktop is geladen, met het geval van elk woord genormaliseerd.

Het eerste exemplaar van 'foo' en het eerste exemplaar van 'too' bepalen de status van 'foo' en 'too' in de rest van de rijen in de Power BI Desktop-tabel. In dit voorbeeld worden alle exemplaren van 'foo' genormaliseerd naar de waarde 'Foo' en worden alle exemplaren van 'too' genormaliseerd naar de waarde 'TOO'.

  • Last updated on