Schema verwerken
Afhankelijk van uw gegevensbron kan informatie over gegevenstypen en kolomnamen al dan niet expliciet worden verstrekt. OData REST API's verwerken dit doorgaans met behulp van de $metadata-definitie. De Power Query-methode verwerkt automatisch het parseren van deze informatie en past deze toe op de gegevens die worden geretourneerd door een OData.Feed OData-bron.
Veel REST API's hebben geen manier om hun schema programmatisch te bepalen. In dergelijke gevallen moet u een schemadefinitie opnemen in uw connector.
Eenvoudige hardcoded aanpak
De eenvoudigste aanpak is het coderen van een schemadefinitie in uw connector. Dit is voldoende voor de meeste gebruiksgevallen.
Het afdwingen van een schema voor de gegevens die door uw connector worden geretourneerd, heeft over het algemeen meerdere voordelen, zoals:
- De juiste gegevenstypen instellen.
- Het verwijderen van kolommen die niet hoeven te worden weergegeven aan eindgebruikers (zoals interne ID's of statusinformatie).
- Ervoor zorgen dat elke pagina met gegevens dezelfde vorm heeft door kolommen toe te voegen die mogelijk ontbreken in een antwoord (REST API's geven meestal aan dat velden null moeten zijn door ze volledig weg te laten).
Het bestaande schema weergeven met Table.Schema
Houd rekening met de volgende code die een eenvoudige tabel retourneert uit de TripPin OData-voorbeeldservice:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
asTable
Notitie
TripPin is een OData-bron, dus realistisch gezien is het logischer om gewoon de automatische schemaafhandeling van de functie OData.Feed te gebruiken. In dit voorbeeld behandelt u de bron als een typische REST API en gebruikt u om de techniek van het hardcoding van een schema met de Web.Contents hand te demonstreren.
Deze tabel is het resultaat:

U kunt de handige functie Table.Schema gebruiken om het gegevenstype van de kolommen te controleren:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
Table.Schema(asTable)

Zowel de luchtvaartmaatschappijcode als de naam zijn van any het type. Table.Schema retourneert een groot aantal metagegevens over de kolommen in een tabel, waaronder namen, posities, typegegevens en veel geavanceerde eigenschappen, zoals Precisie, Schaal en MaxLength. Voor nu moet u zich alleen zorgen maken over het toegekende type ( ), het primitieve type ( ) en of de TypeName Kind kolomwaarde null () kan IsNullable zijn.
Een eenvoudige schematabel definiëren
Uw schematabel bestaat uit twee kolommen:
| Kolom | Details |
|---|---|
| Naam | De naam van de kolom. Dit moet overeenkomen met de naam in de resultaten die door de service worden geretourneerd. |
| Type | Het M-gegevenstype dat u gaat instellen. Dit kan een primitief type zijn (tekst, getal, datum/tijd, etc.) of een toegekend type (Int64.Type, Currency.Type, etc.). |
De hardcoded schematabel voor de tabel stelt de kolommen en in Airlines op en ziet er als volgende AirlineCode Name text uit:
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
Bekijk de volgende schematabellen als u naar een aantal andere eindpunten kijkt:
De Airports tabel heeft vier velden die u wilt behouden (inclusief een van het type record ):
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})
De People tabel heeft zeven velden, waaronder s ( , ), een list Emails AddressInfo null-kolom ( ) Gender Concurrency en een kolom met een toegekend type ( ):
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}
})
U kunt al deze tabellen in één hoofdschematabel SchemaTable zetten:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})

De Helper-functie SchemaTransformTable
De SchemaTransformTable helperfunctie die hieronder wordt beschreven, wordt gebruikt om schema's voor uw gegevens af te dwingen. De volgende parameters worden gebruikt:
| Parameter | Type | Description |
|---|---|---|
| tabel | tabel | De tabel met gegevens waar u uw schema op wilt afdwingen. |
| schema | tabel | De schematabel voor het lezen van kolomgegevens, met het volgende type: type table [Name = text, Type = type] . |
| enforceSchema | getal | (optioneel) Een enum die het gedrag van de functie bepaalt. De standaardwaarde ( ) zorgt ervoor dat de uitvoertabel overeen komt met de schematabel die is opgegeven door ontbrekende kolommen toe te voegen en EnforceSchema.Strict = 1 extra kolommen te verwijderen. De EnforceSchema.IgnoreExtraColumns = 2 optie kan worden gebruikt om extra kolommen in het resultaat te behouden. Wanneer EnforceSchema.IgnoreMissingColumns = 3 wordt gebruikt, worden zowel ontbrekende kolommen als extra kolommen genegeerd. |
De logica voor deze functie ziet er als de volgende uit:
- Bepaal of er kolommen uit de brontabel ontbreken.
- Bepaal of er extra kolommen zijn.
- Negeer gestructureerde kolommen (van het type
list, en ) en kolommen die zijn ingesteld op typerecordtableany. - Gebruik
Table.TransformColumnTypesom elk kolomtype in te stellen. - De volgorde van kolommen wijzigen op basis van de volgorde waarin ze worden weergegeven in de schematabel.
- Stel het type voor de tabel zelf in met behulp van
Value.ReplaceType.
Notitie
Met de laatste stap voor het instellen van het tabeltype hoeft de Power Query-gebruikersinterface geen typegegevens af te leiden bij het weergeven van de resultaten in de query-editor, wat soms kan resulteren in een dubbele aanroep naar de API.
Alles samenbrengen
In de grotere context van een volledige extensie vindt de schemaafhandeling plaats wanneer een tabel wordt geretourneerd door de API. Deze functionaliteit vindt doorgaans plaats op het laagste niveau van de pagineringsfunctie (indien aanwezig), met entiteitsgegevens die worden doorgegeven vanuit een navigatietabel.
Omdat een groot deel van de implementatie van paginerings- en navigatietabellen contextsytemens is, wordt het volledige voorbeeld van het implementeren van een hardcoded mechanisme voor schemaverwerking hier niet weergegeven. In dit TripPin-voorbeeld ziet u hoe een end-to-end-oplossing eruit kan zien.
Geavanceerde benadering
De hardcoded implementatie die hierboven wordt besproken, zorgt ervoor dat schema's consistent blijven voor eenvoudige JSON-repsonses, maar is beperkt tot het parseren van het eerste niveau van het antwoord. Diep geneste gegevenssets zouden baat hebben bij de volgende benadering, die gebruik maakt van M-typen.
Hier is een snelle vernieuwing van typen in de M-taal uit de taalspecificatie:
Een typewaarde is een waarde die andere waarden classificeert. Van een waarde die wordt geclassificeerd door een type wordt gezegd dat deze voldoet aan dat type. Het M-typesysteem bestaat uit de volgende soorten typen:
- Primitieve typen, die primitieve waarden classificeren ( , , , , , , , , , , , , , , ) en ook een aantal
binarydatedatetimedatetimezonedurationlistlogicalnullnumberrecordtexttimetypeabstracte typen bevatten (function, ,tableenanynone).- Recordtypen, waarmee recordwaarden worden geclassificeerd op basis van veldnamen en waardetypen.
- Lijsttypen, waarmee lijsten worden geclassificeerd met één itembasistype.
- Functietypen, waarmee functiewaarden worden geclassificeerd op basis van de typen parameters en retourwaarden.
- Tabeltypen, waarmee tabelwaarden worden geclassificeerd op basis van kolomnamen, kolomtypen en sleutels.
- Null-typen, waarmee de waarde null wordt geclassificeerd naast alle waarden die zijn geclassificeerd op basistype.
- Typetypen, waarmee waarden worden geclassificeerd die typen zijn.
Met behulp van de onbewerkte JSON-uitvoer die u krijgt (en/of door de definities in de $metadata van de serviceop te zoeken), kunt u de volgende recordtypen definiëren om complexe OData-typen weer te geven:
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
];
U ziet hoe LocationType verwijst naar de CityType en om de gestructureerde kolommen weer te LocType geven.
Voor de entiteiten op het hoogste niveau die u wilt vertegenwoordigen als Tabellen, kunt u tabeltypen definiëren:
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
];
Vervolgens kunt u uw variabele bijwerken (die u kunt gebruiken als een opzoektabel voor SchemaTable entiteit-naar-type-toewijzingen) om deze nieuwe typedefinities te gebruiken:
SchemaTable = #table({"Entity", "Type"}, {
{"Airlines", AirlinesType},
{"Airports", AirportsType},
{"People", PeopleType}
});
U kunt vertrouwen op een algemene functie ( ) om een schema af te dwingen voor uw gegevens, net zoals u Table.ChangeType in de vorige oefening hebt SchemaTransformTable gebruikt. In tegenstelling tot wordt een daadwerkelijk M-tabeltype als argument gebruikt en wordt uw SchemaTransformTable Table.ChangeType schema recursief toegepast op alle geneste typen. De handtekening is:
Table.ChangeType = (table, tableType as type) as nullable table => ...
Notitie
Voor flexibiliteit kan de functie worden gebruikt in tabellen, evenals lijsten met records (hoe tabellen worden weergegeven in een JSON-document).
Vervolgens moet u de connectorcode bijwerken om de parameter te wijzigen van een in een en een schema table type aanroep naar toe te Table.ChangeType voegen. Nogmaals, de details hiervan zijn zeer implementatiesyte specifiek en daarom is het hier niet de moeite waard om hier in detail op in te gaan. In dit voorbeeld van een uitgebreide TripPin-connector wordt een end-to-end-oplossing gedemonstreerd die deze geavanceerdere benadering voor het verwerken van schema's implementeert.