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.
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).
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:
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)
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
).
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 list
s (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}
})
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:
- Fastslå om det er noen manglende kolonner fra kildetabellen.
- Avgjør om det er noen ekstra kolonner.
- Ignorer strukturerte kolonner (av typen
list
,record
ogtable
) og kolonnene som er satt til å skrive .any
- Bruk
Table.TransformColumnTypes
denne til å angi hver kolonnetype. - Endre rekkefølgen på kolonner basert på rekkefølgen de vises i skjematabellen.
- 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.
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.
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
,type
time
) og inkluderer også en rekke abstrakte typer (function
,table
,any
ognone
).- 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 SchemaTransformTable
tar 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.