Håndteringsskjema

Avhengig av datakilden din, kan informasjon om datatyper og kolonnenavn bli oppgitt eksplisitt eller ikke. OData REST-API-er håndterer vanligvis dette ved hjelp av $metadata definisjonen, og Power Query-metoden OData.Feed håndterer automatisk parsing av denne informasjonen og anvender den på dataene som returneres fra en OData-kilde.

Mange REST-API-er har ikke en måte å programmere skjemaet sitt på. I disse tilfellene må du inkludere en skjemadefinisjon i kontakten din.

Enkel hardkodet tilnærming

Den enkleste tilnærmingen er å hardkode en skjemadefinisjon inn i kontakten din. Dette er tilstrekkelig for de fleste brukstilfeller.

Alt i alt har det flere fordeler å håndheve et skjema på dataene som returneres av forbindelsen din, som for eksempel:

• Å sette riktige datatyper.
• Fjerning av kolonner som ikke trenger å vises for sluttbrukere (som interne ID-er eller tilstandsinformasjon).
• Sørg for at hver dataside har samme form ved å legge til eventuelle kolonner som kan mangle i et svar (REST-API-er indikerer ofte at felt skal være null ved å utelate dem helt).

Å se på det eksisterende skjemaet med Table.Schema

Vurder følgende kode som returnerer en enkel tabell fra TripPin OData-eksempeltjenesten:

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

Note

TripPin er en OData-kilde, så realistisk sett gir det mer mening å bare bruke OData.Feed funksjonens automatiske skjemahåndtering. I dette eksempelet vil du behandle kildekoden som et typisk REST-API og bruke Web.Contents den til å demonstrere teknikken for å hardkode et skjema for hånd.

Denne tabellen er resultatet:

Du kan bruke den praktiske Table.Schema funksjonen for å sjekke datatypen til kolonnene:

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

Både AirlineCode og Navn er av samme any type. Table.Schema returnerer mye metadata om kolonnene i en tabell, inkludert navn, posisjoner, typeinformasjon og mange avanserte egenskaper som Presisjon, Skala og MaxLengde. Foreløpig bør du kun fokusere på den tilskrevne typen (TypeName), primitive typen (Kind), og om kolonneverdien kan være null (IsNullable).

Definere en enkel skjematabell

Skjematabellen din vil bestå av to kolonner:

Column	Detaljer
Navn	Navnet på kolonnen. Dette må samsvare med navnet i resultatene som tjenesten returnerer.
Type:	M-datatypen du skal setter inn. Dette kan være en primitiv type (tekst, tall, dato, tid og så videre), eller en tilskrevet type (Int64.Type, Currency.Type, og så videre).

Den hardkodede skjematabellen for Airlines tabellen vil sette sine AirlineCode og Name kolonner til text og ser slik ut:

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

Når du ser på noen av de andre endepunktene, vurder følgende skjematabeller:

Tabellen Airports har fire felt du bør beholde (inkludert ett av typen record):

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

Tabellen People har syv felt, inkludert lists (Emails, ), AddressInfoen nullbar kolonne (Gender), og en kolonne med en tilskrevet type (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}
    })

Du kan legge alle disse tabellene i én enkelt masterskjematabell SchemaTable:

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

Helper-funksjonen SchemaTransformTable

HjelpefunksjonenSchemaTransformTable beskrevet nedenfor vil bli brukt til å håndheve skjemaer på dataene dine. Den tar følgende parametere:

Parameter	Type:	Beskrivelse
tabell	tabell	Datatabellen du vil bruke for å håndheve skjemaet ditt på.
skjema	tabell	Skjematabellen for å lese kolonneinformasjon fra, med følgende type: type table [Name = text, Type = type].
enforceSchema	Nummer	(valgfritt) Et enum som styrer funksjonens oppførsel. Standardverdien (EnforceSchema.Strict = 1) sikrer at utdatatabellen matcher skjematabellen som ble levert ved å legge til eventuelle manglende kolonner og fjerne ekstra kolonner. Alternativet EnforceSchema.IgnoreExtraColumns = 2 kan brukes til å bevare ekstra kolonner i resultatet. Når EnforceSchema.IgnoreMissingColumns = 3 den brukes, vil både manglende kolonner og ekstra kolonner bli ignorert.

Logikken for denne funksjonen ser omtrent slik ut:

1. Finn ut om det mangler noen kolonner fra kildetabellen.
2. Finn ut om det finnes noen ekstra kolonner.
3. Ignorer strukturerte kolonner (av typen list, , og table), og kolonner satt til typen anyrecord.
4. Bruk Table.TransformColumnTypes den til å sette hver kolonnetype.
5. Omorganiser kolonnene basert på rekkefølgen de vises i skjematabellen.
6. Sett typen på selve tabellen ved å bruke Value.ReplaceType.

Note

Det siste steget for å sette tabelltypen vil fjerne behovet for at Power Query-grensesnittet skal utlede typeinformasjon når resultatene vises i spørringseditoren, noe som noen ganger kan resultere i et dobbeltanrop til API-et.

Sette alt sammen

I den større konteksten av en komplett utvidelse vil skjemahåndteringen finne sted når en tabell returneres fra API-et. Vanligvis foregår denne funksjonaliteten på det laveste nivået i paging-funksjonen (hvis en slik finnes), med entitetsinformasjon sendt gjennom fra en navigasjonstabell.

Fordi så mye av implementeringen av paging- og navigasjonstabeller er kontekstspesifikk, vil ikke det komplette eksempelet på å implementere en hardkodet skjemahåndteringsmekanisme vises her. Dette TripPin-eksempelet viser hvordan en helhetlig løsning kan se ut.

Sofistikert tilnærming

Den hardkodede implementasjonen som diskuteres ovenfor gjør en god jobb med å sikre at skjemaene forblir konsistente for enkle JSON-repsonser, men den er begrenset til å parse det første nivået av svaret. Dypt nestede datasett vil ha nytte av følgende tilnærming, som utnytter M-typer.

Her er en rask oppdatering om typer i M-språket fra språkspesifikasjonen:

En typeverdi er en verdi som klassifiserer andre verdier. En verdi som klassifiseres etter en type, sies å samsvare med denne typen. M-typesystemet består av følgende typer typer:

• Primitive typer, som klassifiserer primitive verdier (binary, date, , datetimedatetimezone, duration, list, logical, null, texttimenumberrecord) typeog inkluderer også en rekke abstrakte typer (function, table, , any, og none).
• Posttyper, som klassifiserer postverdier basert på feltnavn og verdityper.
• Listetyper, som klassifiserer lister med én enkelt elementbasetype.
• Funksjonstyper, som klassifiserer funksjonsverdier basert på typene til deres parametere og returnerer verdier.
• Tabelltyper, som klassifiserer tabellverdier basert på kolonnenavn, kolonnetyper og nøkler.
• Nullbare typer, som klassifiserer verdien null i tillegg til alle verdiene klassifisert etter en basetype.
• Typetyper, som klassifiserer verdier som er typer.

Ved å bruke rå JSON-utdata du får (og/eller ved å slå opp definisjonene i tjenestens $metadata), kan du definere følgende posttyper for å representere OData-komplekse typer:

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
];

Legg merke til hvordan LocationType den refererer CityType til og LocType for å representere de strukturerte kolonnene.

For de øverste entitetene du vil ha representert som tabeller, kan du definere tabelltyper:

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
];

Du kan deretter oppdatere variabelen din SchemaTable (som du kan bruke som oppslagstabell for entitet-til-type-mappinger) for å bruke disse nye typedefinisjonene:

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

Du kan stole på en felles funksjon (Table.ChangeType) for å håndheve et skjema på dataene dine, omtrent som du brukte SchemaTransformTable i den tidligere øvelsen. I motsetning til SchemaTransformTable, tar en Table.ChangeType faktisk M-tabelltype som et argument, og vil anvende skjemaet ditt rekursivt for alle nestede typer. Dens signatur er:

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

Note

For fleksibilitet kan funksjonen brukes på tabeller så vel som lister over poster (slik tabeller representeres i et JSON-dokument).

Du må deretter oppdatere koblingskoden for å endre parameteren schema fra a table til a type, og legge til et kall til Table.ChangeType. Igjen, detaljene for å gjøre dette er veldig implementasjonsspesifikke og derfor ikke verdt å gå i detalj her. Dette utvidede eksempelet med TripPin-kontakten demonstrerer en ende-til-ende-løsning som implementerer denne mer sofistikerte tilnærmingen til håndtering av skjema.