Bagikan melalui

Kapitalisasi dalam Power Query M

Bekerja dengan data teks terkadang bisa berantakan. Misalnya, nama kota Redmond mungkin diwakili dalam database menggunakan casing yang berbeda ("Redmond", "redmond", dan "REDMOND"). Ini dapat menyebabkan masalah saat mengubah data di Power Query karena bahasa rumus Power Query M sensitif terhadap huruf besar/kecil.

Untungnya, Power Query M menyediakan fungsi untuk membersihkan dan menormalkan kasus data teks. Ada fungsi untuk mengonversi teks ke huruf kecil (abc), huruf besar (ABC), atau huruf yang tepat (Abc). Selain itu, Power Query M juga menyediakan beberapa cara untuk mengabaikan kasus sama sekali.

Artikel ini memperlihatkan kepada Anda cara mengubah kapitalisasi kata dalam teks, daftar, dan tabel. Ini juga menjelaskan berbagai cara untuk mengabaikan huruf besar/kecil saat memanipulasi data dalam teks, daftar, dan tabel. Selain itu, artikel ini membahas cara mengurutkan sesuai dengan kasus.

Mengubah huruf besar/kecil dalam teks

Ada tiga fungsi yang mengonversi teks menjadi huruf kecil, huruf besar, dan huruf besar yang tepat. Fungsinya adalah Text.Lower, , Text.Upperdan Text.Proper. Contoh sederhana berikut menunjukkan bagaimana fungsi-fungsi ini dapat digunakan dalam teks.

Mengonversi semua karakter dalam teks menjadi huruf kecil

Contoh berikut menunjukkan cara mengonversi semua karakter dalam string menjadi huruf kecil.

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

Kode ini menghasilkan output berikut:

the quick brown fox jumps over the lazy dog.

Mengonversi semua karakter dalam teks menjadi huruf besar

Contoh berikut menunjukkan cara mengonversi semua karakter dalam string teks ke huruf besar.

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

Kode ini menghasilkan output berikut:

THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG.

Ubah semua kata menjadi huruf kapital di awal

Contoh berikut menunjukkan cara mengonversi semua kata dalam kalimat menjadi kapitalisasi awal.

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

Kode ini menghasilkan output berikut:

The Quick Brown Fox Jumps Over The Lazy Dog.

Mengubah kasus dalam daftar

Saat mengubah kasus dalam daftar, fungsi yang paling umum digunakan adalah List.Transform. Contoh sederhana berikut menunjukkan bagaimana fungsi ini dapat digunakan dalam daftar.

Mengonversi semua item menjadi huruf kecil

Contoh berikut menunjukkan cara mengubah semua item dalam daftar menjadi huruf kecil.

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

Kode ini menghasilkan output berikut:

Cuplikan layar daftar yang dibuat oleh fungsi transformasi daftar yang menghasilkan hasil huruf kecil.

Ubah semua item menjadi huruf besar

Contoh berikut menunjukkan cara mengubah semua item dalam daftar menjadi huruf besar.

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

Kode ini menghasilkan output berikut:

Cuplikan layar daftar yang dibuat oleh fungsi transformasi daftar yang menghasilkan hasil huruf besar.

Mengonversi semua item ke huruf besar/kecil yang tepat

Contoh berikut menunjukkan cara mengubah semua item dalam daftar menjadi huruf kapital yang benar.

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

Kode ini menghasilkan output berikut:

Cuplikan layar daftar yang dibuat oleh fungsi transformasi daftar yang menghasilkan hasil kasus yang tepat.

Mengubah huruf besar/kecil dalam tabel

Saat mengubah kasus dalam tabel, fungsi yang paling umum untuk digunakan adalah Table.TransformColumns. Ada juga fungsi yang disebut Table.TransformRows yang dapat Anda gunakan untuk mengubah kapitalisasi teks dalam satu baris. Namun, fungsi ini tidak digunakan sesering mungkin.

Contoh sederhana berikut menunjukkan bagaimana Table.TransformColumns fungsi dapat digunakan untuk mengubah kasus dalam tabel.

Mengonversi semua item dalam kolom tabel menjadi huruf kecil

Contoh berikut menunjukkan cara mengubah semua item dalam kolom tabel menjadi huruf kecil, dalam hal ini, nama pelanggan.

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"

Kode ini menghasilkan output berikut:

Cuplikan layar tabel yang dibuat oleh fungsi kolom transformasi tabel yang menghasilkan hasil huruf kecil.

Ubah semua item dalam kolom tabel menjadi huruf besar

Contoh berikut menunjukkan cara mengubah semua item dalam kolom tabel menjadi huruf besar, dalam hal ini, nama buah.

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"

Kode ini menghasilkan output berikut:

Cuplikan layar tabel yang dibuat oleh fungsi transformasi kolom tabel yang menghasilkan hasil dalam huruf besar.

Mengonversi semua item dalam tabel menjadi huruf kapital yang sesuai

Contoh berikut menunjukkan cara mengubah semua item di kedua kolom tabel menjadi huruf besar/kecil yang tepat.

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"

Kode ini menghasilkan output berikut:

Cuplikan layar tabel yang dibuat oleh fungsi kolom transformasi tabel yang menghasilkan hasil kasus yang tepat di kedua kolom.

Mengabaikan kasus

Dalam banyak kasus saat mencari atau mengganti item, Anda mungkin perlu mengabaikan kasus item yang Anda cari. Karena bahasa rumus Power Query M peka huruf besar/kecil, perbandingan antara item yang identik tetapi memiliki kasus yang berbeda menghasilkan identifikasi item yang berbeda, tidak identik. Salah satu metode mengabaikan kasus melibatkan penggunaan Comparer.OrdinalIgnoreCase fungsi dalam fungsi yang menyertakan equationCriteria parameter atau comparer parameter. Metode lain untuk mengabaikan kasus melibatkan penggunaan IgnoreCase opsi (jika tersedia) dalam fungsi yang menyertakan options parameter.

Mengabaikan kapitalisasi huruf dalam teks

Pencarian dalam teks terkadang mengharuskan Anda mengabaikan penggunaan huruf besar/kecil agar dapat menemukan semua instansi teks yang dicari. Fungsi teks umumnya menggunakan fungsi Comparer.OrdinalIgnoreCase pada parameter comparer untuk mengabaikan penggunaan huruf besar/kecil ketika memeriksa kesetaraan.

Contoh berikut menunjukkan cara mengabaikan kasus saat menentukan apakah kalimat berisi kata tertentu, terlepas dari kasusnya.

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

Kode ini menghasilkan output berikut:

true

Contoh berikut menunjukkan cara mengambil posisi awal kemunculan terakhir kata "the" dalam kalimat, terlepas dari kasus.

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

Kode ini menghasilkan output berikut:

34

Mengabaikan kasus dalam daftar

Fungsi daftar apa pun yang berisi parameter opsional equationCriteria dapat menggunakan Comparer.OrdinalIgnoreCase fungsi untuk mengabaikan kasus dalam daftar.

Contoh berikut memeriksa apakah daftar berisi item tertentu, saat mengabaikan kasus. Dalam contoh ini, List.Contains hanya dapat membandingkan satu item dalam daftar, Anda tidak dapat membandingkan daftar dengan daftar. Untuk itu, Anda perlu menggunakan List.ContainsAny.

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

Kode ini menghasilkan output berikut:

true

Contoh berikut memeriksa apakah daftar berisi semua item yang ditentukan dalam parameter kedua (value), saat mengabaikan kasus. Jika ada satu item yang tidak terkandung dalam daftar, seperti cucumber dalam contoh kedua, fungsi mengembalikan FALSE.

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

Kode ini menghasilkan output berikut:

true

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

Kode ini menghasilkan output berikut:

false

Contoh berikut memeriksa apakah salah satu item dalam daftar adalah apel atau pir, dengan mengabaikan perbedaan besar kecil huruf.

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

Kode ini menghasilkan output berikut:

true

Contoh berikut hanya menyimpan item unik, sambil mengabaikan kasus.

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

Kode ini menghasilkan output berikut:

Cuplikan layar dari daftar yang dibuat oleh fungsi pembeda daftar yang mengabaikan huruf besar/kecil.

Dalam contoh sebelumnya, output menampilkan kasus item unik pertama yang ditemukan dalam daftar. Jadi, meskipun ada dua apel (ApPlE dan APPLE), hanya contoh pertama yang ditemukan yang ditampilkan.

Contoh berikut hanya menyimpan item unik saat mengabaikan kasus, tetapi juga mengembalikan semua hasil huruf kecil.

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

Kode ini menghasilkan output berikut:

Cuplikan layar daftar yang dibuat oleh fungsi pembeda daftar saat mengabaikan huruf besar/kecil, namun tetap menampilkan huruf kecil.

Mengabaikan huruf besar/kecil dalam tabel

Tabel memiliki beberapa cara untuk mengabaikan kasus. Fungsi tabel seperti Table.Contains, Table.Distinct, dan Table.PositionOf semua berisi equationCriteria parameter. Parameter ini dapat menggunakan Comparer.OrdinalIgnoreCase fungsi untuk mengabaikan kasus dalam tabel, dengan cara yang sama seperti daftar di bagian sebelumnya. Fungsi tabel, seperti Table.MatchesAnyRows yang berisi condition parameter dapat juga digunakan Comparer.OrdinalIgnoreCase yang dibungkus dalam fungsi tabel lain untuk mengabaikan perbedaan huruf besar dan kecil. Fungsi tabel lainnya, khusus untuk pencocokan fuzzy, dapat menggunakan IgnoreCase opsi .

Contoh berikut menunjukkan cara memilih baris tertentu yang berisi kata "pir" dengan mengabaikan perbedaan huruf besar dan kecil. Contoh ini menggunakan condition parameter Table.SelectRows dengan Text.Contains sebagai kondisi untuk membuat perbandingan saat mengabaikan kasus.

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"

Kode ini menghasilkan output berikut:

Cuplikan layar tabel yang dibuat oleh fungsi pilih baris tabel saat mengabaikan perbedaan huruf besar dan kecil.

Contoh berikut menunjukkan cara menentukan apakah salah satu baris dalam tabel berisi pear dalam FRUIT kolom. Contoh ini menggunakan Comparer.OrdinalIgnoreCase dalam fungsi Text.Contains dengan menggunakan parameter condition dari fungsi Table.MatchesAnyRows.

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"

Kode ini menghasilkan output berikut:

true

Contoh berikut menunjukkan cara mengambil tabel dengan nilai yang dimasukkan oleh pengguna yang berisi kolom yang mencantumkan buah favorit mereka, tanpa format yang ditetapkan. Kolom ini pertama-tama dicocokkan secara fuzzy untuk mengekstrak nama buah favorit mereka, yang kemudian ditampilkan di kolom tersendiri yang bernama Cluster. Kemudian kolom Kluster diperiksa untuk menentukan buah-buahan yang berbeda dan unik yang ada di kolom. Setelah buah-buahan unik ditentukan, langkah terakhir adalah mengubah semua nama buah menjadi huruf kecil.

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"

Kode ini menghasilkan output berikut:

Cuplikan layar tabel yang dibuat oleh pencocokan fuzzy saat mengabaikan kasus.

Kasus dan pengurutan

Daftar dan tabel dapat diurutkan menggunakan List.Sort atau Table.Sort masing-masing. Namun, mengurutkan teks tergantung pada kasus item terkait dalam daftar atau tabel untuk menentukan urutan pengurutan aktual (naik atau turun).

Bentuk pengurutan yang paling umum menggunakan teks yang semuanya huruf kecil, semua huruf besar, atau huruf besar yang tepat. Jika ada campuran kasus-kasus ini, urutan urutan naik adalah sebagai berikut:

  1. Teks apa pun dalam kolom daftar atau tabel yang dimulai dengan huruf kapital adalah pertama.
  2. Jika ada teks yang cocok, tetapi satu menggunakan huruf kapital pada tempatnya dan yang lain semuanya huruf kapital, versi huruf kapital ditempatkan terlebih dahulu.
  3. Huruf kecil kemudian diurutkan.

Untuk urutan turun, langkah-langkah yang tercantum sebelumnya diproses secara terbalik.

Misalnya, sampel berikut memiliki campuran semua huruf kecil, semua huruf besar, dan teks dengan huruf awal kapital yang akan diurutkan dalam urutan menaik.

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

Kode ini menghasilkan output berikut:

Cuplikan layar daftar yang dibuat dengan mengurutkan teks dengan semua huruf kecil, semua huruf kapital, dan teks kapital benar.

Meskipun tidak umum, Anda mungkin memiliki campuran huruf besar dan kecil dalam teks untuk diurutkan. Urutan urutan naik dalam kasus ini adalah:

  1. Teks apa pun dalam kolom daftar atau tabel yang dimulai dengan huruf kapital adalah pertama.
  2. Jika ada teks yang cocok, teks dengan jumlah maksimum huruf besar di sebelah kiri akan diproses berikutnya.
  3. Huruf kecil kemudian diurutkan, dengan jumlah maksimum huruf besar ke kanan dilakukan terlebih dahulu.

Bagaimanapun, mungkin lebih nyaman untuk mengonversi teks ke kasus yang konsisten sebelum mengurutkan.

Normalisasi Power BI Desktop

Power Query M sensitif terhadap huruf besar/kecil dan membedakan antara kapitalisasi berbeda dari teks yang sama. Misalnya, "Foo", "foo", dan "FOO" diperlakukan sebagai entitas yang berbeda. Namun, ketika data dimuat ke Power BI Desktop, nilai teks dinormalisasi, yang berarti Power BI Desktop memperlakukannya sebagai nilai yang sama terlepas dari kapitalisasinya. Oleh karena itu, jika Anda perlu mengubah data sambil mempertahankan sensitivitas kasus dalam data, Anda harus menangani transformasi data di Power Query sebelum memuat data ke Power BI Desktop.

Misalnya, tabel berikut di Power Query memperlihatkan kasus yang berbeda di setiap baris tabel.

Cuplikan layar tabel yang berisi teks foo dan too dengan berbagai format.

Saat tabel ini dimuat ke Power BI Desktop, nilai teks menjadi dinormalisasi, menghasilkan tabel berikut.

Cuplikan layar tabel yang sama dimuat di Power BI Desktop, dengan kasus setiap kata dinormalisasi.

Instans pertama "foo" dan instans pertama "too" menentukan kasus "foo" dan "too" di seluruh baris lainnya dalam tabel Power BI Desktop. Dalam contoh ini, semua instans "foo" dinormalisasi ke nilai "Foo" dan semua instans "too" dinormalisasi ke nilai "TOO".