Les på engelsk

Del via


Håndtering av skjema

Avhengig av datakilden kan det hende at informasjon om datatyper og kolonnenavn ikke er angitt eksplisitt. OData REST-API-er håndterer vanligvis dette ved hjelp av $metadata definisjonen, og Power Query-metoden OData.Feed håndterer automatisk analyse av denne informasjonen og bruker den på dataene som returneres fra en OData-kilde.

Mange REST-API-er har ingen måte å programmatisk bestemme skjemaet på. I slike tilfeller må du inkludere en skjemadefinisjon i koblingen.

Enkel hardkodet tilnærming

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

Samlet sett har bruk av et skjema på dataene som returneres av koblingen, flere fordeler, for eksempel:

  • Angi de riktige datatypene.
  • Fjerne kolonner som ikke trenger å vises for sluttbrukere (for eksempel interne ID-er eller tilstandsinformasjon).
  • Sikre at hver side med data har samme figur ved å legge til kolonner som kan mangle fra et svar (REST-API-er angir vanligvis at feltene skal være null ved å utelate dem helt).

Vise 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

Obs!

TripPin er en OData-kilde, så realistisk ville det være mer fornuftig å bare bruke OData.Feed funksjonens automatiske skjemahåndtering. I dette eksemplet skal du behandle kilden som en typisk REST-API og bruke Web.Contents til å demonstrere teknikken for hardkoding av et skjema for hånd.

Denne tabellen er resultatet:

Tabell over TripPin Airline-data.

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

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

Resultatet av Table.Schema brukt på TripPin Airline-data.

Både AirlineCode og Name er av typen any . Table.Schema returnerer mange metadata om kolonnene i en tabell, inkludert navn, posisjoner, typeinformasjon og mange avanserte egenskaper, for eksempel Presisjon, Skalering og MaxLength. Foreløpig bør du bare bekymre deg med den tilskrevne typen (TypeName), primitiv type (Kind) og om kolonneverdien kan være null (IsNullable).

Definere en enkel skjematabell

Skjematabellen består av to kolonner:

Kolonne Detaljer
Name Navnet på kolonnen. Dette må samsvare med navnet i resultatene som returneres av tjenesten.
Type M-datatypen du skal angi. Dette kan være en primitiv type (tekst, tall, datetime og så videre) eller en tilskrevet type (Int64.Type, Currency.Type og så videre).

Den hardkodede skjematabellen Airlines for tabellen angir AirlineCode kolonnene og Name kolonnene 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, bør du vurdere følgende skjematabeller:

Tabellen Airports har fire felt du vil beholde (inkludert én av typene record):

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

Tabellen People har sju felt, inkludert lists (Emails, AddressInfo), en kolonne som kan nullstilles (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 plassere alle disse tabellene i én enkelt hovedskjematabell SchemaTable:

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

Tabell over skjemaer.

SchemaTransformTable-hjelpefunksjonen

Hjelpefunksjonen SchemaTransformTable som er beskrevet nedenfor, brukes til å fremtvinge skjemaer på dataene. Det tar følgende parametere:

Parameter Type Bekrivelse
tabellen tabellen Tabellen med data du vil fremtvinge skjemaet på.
schema tabellen Skjematabellen du vil lese kolonneinformasjon fra, med følgende type: type table [Name = text, Type = type].
enforceSchema Nummer (valgfritt) En opplisting som styrer virkemåten til funksjonen.
Standardverdien (EnforceSchema.Strict = 1) sikrer at utdatatabellen samsvarer med skjematabellen som ble angitt 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 brukes, ignoreres både manglende kolonner og ekstra kolonner.

Logikken for denne funksjonen ser omtrent slik ut:

  1. Fastslå om det er noen manglende kolonner fra kildetabellen.
  2. Avgjør om det er noen ekstra kolonner.
  3. Ignorer strukturerte kolonner (av typen list, recordog table) og kolonnene som er satt til å skrive .any
  4. Bruk Table.TransformColumnTypes denne til å angi hver kolonnetype.
  5. Endre rekkefølgen på kolonner basert på rekkefølgen de vises i skjematabellen.
  6. Angi typen i selve tabellen ved hjelp av Value.ReplaceType.

Obs!

Det siste trinnet for å angi tabelltypen vil fjerne behovet for at Brukergrensesnittet for Power Query skal utlede typeinformasjon når du viser resultatene i redigeringsprogrammet for spørringen, noe som noen ganger kan føre til et dobbeltkall til API-en.

Sette alt sammen

I den større konteksten av en fullstendig utvidelse vil skjemabehandlingen finne sted når en tabell returneres fra API-en. Vanligvis foregår denne funksjonaliteten på det laveste nivået i sidevekslingsfunksjonen (hvis det finnes), med enhetsinformasjon som sendes gjennom fra en navigasjonstabell.

Fordi så mye av implementeringen av sidevekslings- og navigasjonstabeller er kontekstspesifikke, vises ikke det fullstendige eksemplet på implementering av en hardkodet skjemahåndteringsmekanisme her. Dette TripPin-eksemplet viser hvordan en ende-til-ende-løsning kan se ut.

Avansert tilnærming

Den hardkodede implementeringen som er diskutert ovenfor, gjør en god jobb med å sørge for at skjemaer forblir konsekvente for enkle JSON-repsonser, men det er begrenset til å analysere det første nivået i svaret. Dypt nestede datasett vil dra nytte av følgende tilnærming, som drar nytte av M-typer.

Her er en rask oppdatering av typer på 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, , datetime, datetimezone, duration, list, logical, null, number, record, text, typetime) og inkluderer også en rekke abstrakte typer (function, table, anyog none).
  • Posttyper som klassifiserer postverdier basert på feltnavn og verdityper.
  • Listetyper som klassifiserer lister ved hjelp av en basistype for enkeltelement.
  • Funksjonstyper som klassifiserer funksjonsverdier basert på typene parametere og returverdier.
  • Tabelltyper som klassifiserer tabellverdier basert på kolonnenavn, kolonnetyper og nøkler.
  • Nullverdityper, som klassifiserer verdien null i tillegg til alle verdiene klassifisert etter en basistype.
  • Typetyper som klassifiserer verdier som er typer.

Ved hjelp av de rå JSON-utdataene 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 refererer CityType til og LocType representerer de strukturerte kolonnene.

For de øverste enhetene du vil representere 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
];

Deretter kan du oppdatere variabelen SchemaTable (som du kan bruke som oppslagstabell for enhet-til-type-tilordninger) til å bruke disse nye typedefinisjonene:

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

Du kan stole på en felles funksjon (Table.ChangeType) for å fremtvinge et skjema på dataene dine, omtrent som du brukte SchemaTransformTable i den tidligere øvelsen. I motsetning til SchemaTransformTabletar Table.ChangeType du en faktisk M-tabelltype som et argument, og bruker skjemaet rekursivt for alle nestede typer. Signaturen er:

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

Obs!

For fleksibilitet kan funksjonen brukes i tabeller samt lister over poster (som er hvordan tabeller representeres i et JSON-dokument).

Deretter må du oppdatere koblingskoden for å endre parameteren schema fra en table til en type, og legge til et anrop i Table.ChangeType. Igjen, detaljene for å gjøre det er svært implementeringsspesifikke og dermed ikke verdt å gå i detalj her. Dette utvidede TripPin-koblingseksemplet demonstrerer en ende-til-ende-løsning som implementerer denne mer avanserte tilnærmingen til håndtering av skjema.