Baca dalam bahasa Inggris

Bagikan melalui


Menangani skema

Bergantung pada sumber data Anda, informasi tentang jenis data dan nama kolom mungkin atau mungkin tidak disediakan secara eksplisit. REST API OData biasanya menangani ini menggunakan definisi $metadata, dan metode Power Query OData.Feed secara otomatis menangani penguraian informasi ini dan menerapkannya ke data yang dikembalikan dari sumber OData.

Banyak REST API tidak memiliki cara untuk menentukan skema mereka secara terprogram. Dalam kasus ini, Anda harus menyertakan definisi skema dalam konektor Anda.

Pendekatan hardcoded sederhana

Pendekatan paling sederhana adalah dengan hardcode definisi skema ke dalam konektor Anda. Ini cukup untuk sebagian besar kasus penggunaan.

Secara keseluruhan, memberlakukan skema pada data yang dikembalikan oleh konektor Anda memiliki beberapa manfaat, seperti:

  • Mengatur jenis data yang benar.
  • Menghapus kolom yang tidak perlu ditampilkan kepada pengguna akhir (seperti ID internal atau informasi status).
  • Memastikan bahwa setiap halaman data memiliki bentuk yang sama dengan menambahkan kolom apa pun yang mungkin hilang dari respons (REST API biasanya menunjukkan bahwa bidang harus null dengan menghilangkannya sepenuhnya).

Menampilkan skema yang ada dengan Table.Schema

Pertimbangkan kode berikut yang mengembalikan tabel sederhana dari layanan sampel TripPin OData:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    asTable

Catatan

TripPin adalah sumber OData, jadi secara realistis akan lebih masuk akal untuk hanya menggunakan OData.Feed penanganan skema otomatis fungsi. Dalam contoh ini Anda akan memperlakukan sumber sebagai REST API biasa dan menggunakan Web.Contents untuk menunjukkan teknik hardcoding skema dengan tangan.

Tabel ini adalah hasilnya:

Tabel data TripPin Airline.

Anda dapat menggunakan fungsi yang Table.Schema berguna untuk memeriksa jenis data kolom:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    Table.Schema(asTable)

Hasil Table.Schema diterapkan ke data TripPin Airline.

Baik AirlineCode maupun Nama berjenis any . Table.Schema mengembalikan banyak metadata tentang kolom dalam tabel, termasuk nama, posisi, informasi jenis, dan banyak properti tingkat lanjut seperti Presisi, Skala, dan MaxLength. Untuk saat ini Anda hanya perlu memperhatikan diri Anda dengan jenis yang ditulis (TypeName), jenis primitif (Kind), dan apakah nilai kolom mungkin null (IsNullable).

Menentukan tabel skema sederhana

Tabel skema Anda akan terdiri dari dua kolom:

Kolom Detail
Nama Nama kolom. Ini harus cocok dengan nama dalam hasil yang dikembalikan oleh layanan.
Jenis Jenis data M yang akan Anda atur. Ini bisa menjadi jenis primitif (teks, angka, tanggalwaktu, dan sebagainya), atau jenis yang ditulis (Int64.Type, Currency.Type, dan sebagainya).

Tabel skema hardcoded untuk Airlines tabel akan mengatur AirlineCode kolom dan Name ke text dan terlihat seperti ini:

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    })

Saat Anda melihat ke beberapa titik akhir lainnya, pertimbangkan tabel skema berikut:

Tabel Airports memiliki empat bidang yang ingin Anda simpan (termasuk salah satu jenis record):

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    })

Tabel People memiliki tujuh bidang, termasuk lists (Emails, AddressInfo), kolom nullable (Gender), dan kolom dengan jenis yang ditulis (Concurrency):

People = #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}
    })

Anda dapat memasukkan semua tabel ini ke dalam satu tabel SchemaTableskema master :

SchemaTable = #table({"Entity", "SchemaTable"}, {
        {"Airlines", Airlines},
        {"Airports", Airports},
        {"People", People}
    })

Tabel skema.

Fungsi pembantu SchemaTransformTable

Fungsi pembantu SchemaTransformTable yang dijelaskan di bawah ini akan digunakan untuk memberlakukan skema pada data Anda. Prosedur ini membutuhkan parameter berikut:

Parameter Jenis Deskripsi
tabel tabel Tabel data yang ingin Anda tertibkan skemanya.
skema tabel Tabel skema untuk membaca info kolom dari, dengan jenis berikut: type table [Name = text, Type = type].
enforceSchema number (opsional) Enum yang mengontrol perilaku fungsi.
Nilai default (EnforceSchema.Strict = 1) memastikan bahwa tabel output akan cocok dengan tabel skema yang disediakan dengan menambahkan kolom yang hilang, dan menghapus kolom tambahan.
Opsi EnforceSchema.IgnoreExtraColumns = 2 dapat digunakan untuk mempertahankan kolom tambahan dalam hasilnya.
Saat EnforceSchema.IgnoreMissingColumns = 3 digunakan, kolom yang hilang dan kolom tambahan akan diabaikan.

Logika untuk fungsi ini terlihat seperti ini:

  1. Tentukan apakah ada kolom yang hilang dari tabel sumber.
  2. Tentukan apakah ada kolom tambahan.
  3. Abaikan kolom terstruktur (dari jenis list, , recorddan table), dan kolom yang diatur ke jenis any.
  4. Gunakan Table.TransformColumnTypes untuk mengatur setiap jenis kolom.
  5. Urutkan ulang kolom berdasarkan urutan yang muncul dalam tabel skema.
  6. Atur jenis pada tabel itu sendiri menggunakan Value.ReplaceType.

Catatan

Langkah terakhir untuk mengatur jenis tabel akan menghapus kebutuhan UI Power Query untuk menyimpulkan informasi jenis saat menampilkan hasilnya di editor kueri, yang terkadang dapat mengakibatkan panggilan ganda ke API.

Merangkum semuanya

Dalam konteks ekstensi lengkap yang lebih besar, penanganan skema akan terjadi ketika tabel dikembalikan dari API. Biasanya fungsionalitas ini berlangsung pada tingkat terendah fungsi penomoran (jika ada), dengan informasi entitas diteruskan dari tabel navigasi.

Karena begitu banyak implementasi paging dan tabel navigasi khusus konteks, contoh lengkap penerapan mekanisme penanganan skema hardcoded tidak akan ditampilkan di sini. Contoh TripPin ini menunjukkan bagaimana solusi end-to-end mungkin terlihat.

Pendekatan canggih

Implementasi hardcoded yang dibahas di atas melakukan pekerjaan yang baik untuk memastikan bahwa skema tetap konsisten untuk repson JSON sederhana, tetapi terbatas pada penguraian tingkat pertama respons. Himpunan data yang sangat berlapis akan mendapat manfaat dari pendekatan berikut, yang memanfaatkan Jenis M.

Berikut adalah refresh cepat tentang jenis dalam bahasa M dari Spesifikasi Bahasa:

Nilai jenis adalah nilai yang mengklasifikasikan nilai lain. Nilai yang diklasifikasikan oleh jenis dikatakan sesuai dengan jenis tersebut. Sistem jenis M terdiri dari jenis berikut:

  • Jenis primitif, yang mengklasifikasikan nilai primitif (binary, date, , datetime, durationdatetimezone, list, logical, null, numberrecordtimetext) typedan juga menyertakan sejumlah jenis abstrak (function, , tableany, dan none).
  • Jenis catatan, yang mengklasifikasikan nilai rekaman berdasarkan nama bidang dan jenis nilai.
  • Jenis daftar, yang mengklasifikasikan daftar menggunakan jenis dasar item tunggal.
  • Jenis fungsi, yang mengklasifikasikan nilai fungsi berdasarkan jenis parameternya dan mengembalikan nilai.
  • Jenis tabel, yang mengklasifikasikan nilai tabel berdasarkan nama kolom, jenis kolom, dan kunci.
  • Jenis nullable, yang mengklasifikasikan nilai null selain semua nilai yang diklasifikasikan oleh jenis dasar.
  • Jenis jenis, yang mengklasifikasikan nilai yang merupakan jenis.

Menggunakan output JSON mentah yang Anda dapatkan (dan/atau dengan mencari definisi dalam $metadata layanan), Anda dapat menentukan jenis rekaman berikut untuk mewakili jenis kompleks OData:

LocationType = type [
    Address = text,
    City = CityType,
    Loc = LocType
];

CityType = type [
    CountryRegion = text,
    Name = text,
    Region = text
];

LocType = type [
    #"type" = text,
    coordinates = {number},
    crs = CrsType
];

CrsType = type [
    #"type" = text,
    properties = record
];

Perhatikan bagaimana LocationType mereferensikan CityType dan LocType untuk mewakili kolom terstrukturnya.

Untuk entitas tingkat atas yang ingin Anda tunjuk sebagai Tabel, Anda dapat menentukan jenis tabel:

AirlinesType = type table [
    AirlineCode = text,
    Name = text
];
AirportsType = type table [
    Name = text,
    IataCode = text,
    Location = LocationType
];
PeopleType = type table [
    UserName = text,
    FirstName = text,
    LastName = text,
    Emails = {text},
    AddressInfo = {nullable LocationType},
    Gender = nullable text,
    Concurrency  Int64.Type
];

Anda kemudian dapat memperbarui variabel Anda SchemaTable (yang dapat Anda gunakan sebagai tabel pencarian untuk pemetaan entitas-ke-jenis) untuk menggunakan definisi jenis baru ini:

SchemaTable = #table({"Entity", "Type"}, {
    {"Airlines", AirlinesType},
    {"Airports", AirportsType},
    {"People", PeopleType}
});

Anda dapat mengandalkan fungsi umum (Table.ChangeType) untuk menerapkan skema pada data Anda, sama seperti yang Anda gunakan SchemaTransformTable di latihan sebelumnya. Tidak seperti SchemaTransformTable, Table.ChangeType mengambil jenis tabel M aktual sebagai argumen, dan akan menerapkan skema Anda secara rekursif untuk semua jenis berlapis. Tanda tangannya adalah:

Table.ChangeType = (table, tableType as type) as nullable table => ...

Catatan

Untuk fleksibilitas, fungsi dapat digunakan pada tabel serta daftar rekaman (yaitu bagaimana tabel diwakili dalam dokumen JSON).

Anda kemudian perlu memperbarui kode konektor untuk mengubah schema parameter dari menjadi table type, dan menambahkan panggilan ke Table.ChangeType. Sekali lagi, detail untuk melakukannya sangat spesifik implementasi dan dengan demikian tidak layak masuk secara rinci di sini. Contoh konektor TripPin yang diperluas ini menunjukkan solusi end-to-end yang menerapkan pendekatan yang lebih canggih ini untuk menangani skema.