TripPin del 6 - Skjema

Denne flerdelte opplæringen dekker opprettelsen av en ny datakildeutvidelse for Power Query. Opplæringen er ment å gjøres sekvensielt – hver leksjon bygger på koblingen som er opprettet i tidligere leksjoner, og legger trinnvis til nye funksjoner i koblingen.

I denne leksjonen vil du:

• Definere et fast skjema for en REST-API
• Dynamisk angi datatyper for kolonner
• Fremtving en tabellstruktur for å unngå transformasjonsfeil på grunn av manglende kolonner
• Skjule kolonner fra resultatsettet

En av de store fordelene med en OData-tjeneste over en standard REST-API er den $metadata definisjonen. Dokumentet $metadata beskriver dataene som finnes i denne tjenesten, inkludert skjemaet for alle enhetene (tabeller) og felt (kolonner). Funksjonen OData.Feed bruker denne skjemadefinisjonen til automatisk å angi datatypeinformasjon, slik at sluttbrukerne får datoer, heltall, klokkeslett og så videre, i stedet for å hente alle tekst- og tallfeltene (som du ville gjort fra Json.Document), får sluttbrukerne datoer, heltall, klokkeslett og så videre, noe som gir en bedre generell brukeropplevelse.

Mange REST-API-er har ingen måte å programmatisk bestemme skjemaet på. I slike tilfeller må du inkludere skjemadefinisjoner i koblingen. I denne leksjonen skal du definere et enkelt, hardkodet skjema for hver av tabellene, og fremtvinge skjemaet på dataene du leser fra tjenesten.

Merk

Fremgangsmåten som er beskrevet her, bør fungere for mange REST-tjenester. Fremtidige leksjoner bygger på denne tilnærmingen ved å rekursivt håndheve skjemaer på strukturerte kolonner (post, liste, tabell), og gi eksempelimplementeringer som programmatisk kan generere en skjematabell fra CSDL- eller JSON-skjemadokumenter .

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

• Angi riktige datatyper
• 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 (en vanlig måte for REST-API-er å angi at et felt skal være null)

Vise det eksisterende skjemaet med Table.Schema

Koblingen som ble opprettet i forrige leksjon, viser tre tabeller fra TripPin-tjenesten –AirlinesAirports og People. Kjør følgende spørring for å vise Airlines tabellen:

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    data

I resultatene ser du fire kolonner som returneres:

• @odata.id
• @odata.editLink
• AirlineCode
• Name

Kolonnene @odata*er en del av OData-protokollen, og ikke noe du ønsker eller trenger å vise til sluttbrukerne av koblingen. AirlineCode og Name er de to kolonnene du vil beholde. Hvis du ser på skjemaet i tabellen (ved hjelp av den praktiske Table.Schema-funksjonen ), kan du se at alle kolonnene i tabellen har en datatype Any.Type.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Table.Schema returnerer mange metadata om kolonnene i en tabell, inkludert navn, posisjoner, typeinformasjon og mange avanserte egenskaper, for eksempel Presisjon, Skalering og MaxLength. Fremtidige leksjoner gir utformingsmønstre for å angi disse avanserte egenskapene, men foreløpig trenger 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 (text, number, datetimeog så videre) eller en tilskrevet type (Int64.Type, Currency.Typeog så videre).

Den hardkodede skjematabellen Airlines for tabellen setter AirlineCode dens og Name kolonnene til text, og ser slik ut:

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

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}
    });

People Til slutt har tabellen sju felt, inkludert lister (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}
    })

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 type any.
4. Bruk Table.TransformColumnTypes 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.

Merk

Det siste trinnet for å angi tabelltypen fjerner behovet for at Brukergrensesnittet for Power Query skal utlede typeinformasjon når du viser resultatene i redigeringsprogrammet for spørringen. Dette fjerner problemet med dobbel forespørsel du så på slutten av forrige opplæring.

Følgende hjelpekode kan kopieres og limes inn i utvidelsen:

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2;   // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
    let
        // Default to EnforceSchema.Strict
        _enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

        // Applies type transforms to a given table
        EnforceTypes = (table as table, schema as table) as table =>
            let
                map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
                mapped = Table.TransformColumns(schema, {"Type", map}),
                omitted = Table.SelectRows(mapped, each [Type] <> null),
                existingColumns = Table.ColumnNames(table),
                removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
                primativeTransforms = Table.ToRows(removeMissing),
                changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
            in
                changedPrimatives,

        // Returns the table type for a given schema
        SchemaToTableType = (schema as table) as type =>
            let
                toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
                toRecord = Record.FromList(toList, schema[Name]),
                toType = Type.ForRecord(toRecord, false)
            in
                type table (toType),

        // Determine if we have extra/missing columns.
        // The enforceSchema parameter determines what we do about them.
        schemaNames = schema[Name],
        foundNames = Table.ColumnNames(table),
        addNames = List.RemoveItems(schemaNames, foundNames),
        extraNames = List.RemoveItems(foundNames, schemaNames),
        tmp = Text.NewGuid(),
        added = Table.AddColumn(table, tmp, each []),
        expanded = Table.ExpandRecordColumn(added, tmp, addNames),
        result = if List.IsEmpty(addNames) then table else expanded,
        fullList =
            if (_enforceSchema = EnforceSchema.Strict) then
                schemaNames
            else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
                foundNames
            else
                schemaNames & extraNames,

        // Select the final list of columns.
        // These will be ordered according to the schema table.
        reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
        enforcedTypes = EnforceTypes(reordered, schema),
        withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
    in
        withType;

Oppdatere TripPin-koblingen

Du vil nå gjøre følgende endringer i koblingen for å bruke den nye kode for skjemahåndhevelse.

1. Definer en hovedskjematabell (SchemaTable) som inneholder alle skjemadefinisjonene.
2. TripPin.FeedOppdater , GetPageog GetAllPagesByNextLink godta en schema parameter.
3. Fremtving skjemaet i GetPage.
4. Oppdater navigasjonstabellkoden for å bryte hver tabell med et kall til en ny funksjon (GetEntity)– dette gir deg mer fleksibilitet til å manipulere tabelldefinisjonene i fremtiden.

Hovedskjematabell

Nå konsoliderer du skjemadefinisjonene i én enkelt tabell, og legger til en hjelpefunksjon (GetSchemaForEntity) som lar deg slå opp definisjonen basert på et enhetsnavn (for eksempel GetSchemaForEntity("Airlines")).

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

    {"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}
    })}
});

GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";

Legge til skjemastøtte i datafunksjoner

Nå legger du til en valgfri schema parameter i TripPin.Feedfunksjonene , GetPageog .GetAllPagesByNextLink Dette gjør at du kan sende skjemaet (når du vil) til sidevekslingsfunksjonene, der det vil bli brukt på resultatene du får tilbake fra tjenesten.

TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...

Du oppdaterer også alle kall til disse funksjonene for å sikre at du sender skjemaet gjennom riktig.

Fremtvinge skjemaet

Den faktiske skjemahåndhevelse vil bli gjort i din GetPage funksjon.

GetPage = (url as text, optional schema as table) as table =>
    let
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[value]),
        // enforce the schema
        withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
    in
        withSchema meta [NextLink = nextLink];

Merk

Denne GetPage implementeringen bruker Table.FromRecords til å konvertere listen over poster i JSON-svaret til en tabell. En stor ulempe med å bruke Table.FromRecords er at det forutsetter at alle postene i listen har samme sett med felt. Dette fungerer for TripPin-tjenesten, siden OData-postene er garantert å inneholde de samme feltene, men dette er kanskje ikke tilfelle for alle REST-API-er. En mer robust implementering ville brukt en kombinasjon av Table.FromList og Table.ExpandRecordColumn. Senere opplæringer endrer implementeringen for å hente kolonnelisten fra skjematabellen, slik at ingen kolonner går tapt eller mangler under JSON til M-oversettelsen.

Legge til GetEntity-funksjonen

Funksjonen GetEntity vil bryte samtalen til TripPin.Feed. Det vil slå opp en skjemadefinisjon basert på enhetsnavnet, og bygge den fullstendige url-adressen for forespørselen.

GetEntity = (url as text, entity as text) as table => 
    let
        fullUrl = Uri.Combine(url, entity),
        schemaTable = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schemaTable)
    in
        result;

Deretter oppdaterer du funksjonen til TripPinNavTable å ringe GetEntity, i stedet for å foreta alle anropene innebygd. Den største fordelen med dette er at den lar deg fortsette å endre enhetsbyggingskoden uten å måtte berøre navigasjonstabelllogikken.

TripPinNavTable = (url as text) as table =>
    let
        entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
        rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

Sette alt sammen

Når alle kodeendringene er gjort, kan du kompilere og kjøre testspørringen som krever Table.Schema Airlines-tabellen.

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Du ser nå at Airlines-tabellen bare har de to kolonnene du definerte i skjemaet:

Hvis du kjører den samme koden mot Folk tabellen...

let
    source = TripPin.Contents(),
    data = source{[Name="People"]}[Data]
in
    Table.Schema(data)

Du vil se at den tilskrevne typen du brukte (Int64.Type) også ble riktig angitt.

En viktig ting å merke seg er at denne implementeringen av SchemaTransformTable ikke endrer typene list og record kolonnene, men Emails kolonnene skrives AddressInfo fortsatt inn som list. Dette er fordi Json.Document JSON-matriser tilordnes til M-lister på riktig måte, og JSON-objekter til M-poster. Hvis du skulle utvide listen eller postkolonnen i Power Query, ville du se at alle de utvidede kolonnene vil være av typen. Fremtidige opplæringer vil forbedre implementeringen for å angi typeinformasjon for nestede komplekse typer på nytt.

Konklusjon

Denne opplæringen ga et eksempel på implementering for å fremtvinge et skjema på JSON-data som returneres fra en REST-tjeneste. Selv om dette eksemplet bruker et enkelt hardkodet skjematabellformat, kan tilnærmingen utvides ved å dynamisk bygge en skjematabelldefinisjon fra en annen kilde, for eksempel en JSON-skjemafil, eller metadatatjeneste/endepunkt som vises av datakilden.

I tillegg til å endre kolonnetyper (og verdier) angir koden også riktig typeinformasjon i selve tabellen. Hvis du angir denne typen informasjon, fordeler ytelsen når du kjører i Power Query, da brukeropplevelsen alltid forsøker å utlede typeinformasjon for å vise de riktige grensesnittkøene til sluttbrukeren, og slutningsanropene kan ende opp med å utløse andre kall til de underliggende data-API-ene.

Hvis du viser Folk tabellen ved hjelp av TripPin-koblingen fra forrige leksjon, ser du at alle kolonnene har et «skriv inn et hvilket som helst»-ikon (selv kolonnene som inneholder lister):

Når du kjører den samme spørringen med TripPin-koblingen fra denne leksjonen, ser du nå at typeinformasjonen vises riktig.

Neste trinn

TripPin Del 7 – Avansert skjema med M-typer