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 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).
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:
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)
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
).
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 list
s (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 SchemaTable
skema master :
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
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:
- Tentukan apakah ada kolom yang hilang dari tabel sumber.
- Tentukan apakah ada kolom tambahan.
- Abaikan kolom terstruktur (dari jenis
list
, ,record
dantable
), dan kolom yang diatur ke jenisany
. - Gunakan
Table.TransformColumnTypes
untuk mengatur setiap jenis kolom. - Urutkan ulang kolom berdasarkan urutan yang muncul dalam tabel skema.
- 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.
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.
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
,duration
datetimezone
,list
,logical
,null
,number
record
time
text
)type
dan juga menyertakan sejumlah jenis abstrak (function
, ,table
any
, dannone
).- 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.