Manipulando esquema

Dependendo da fonte de dados, as informações sobre tipos de dados e nomes de coluna podem ou não ser fornecidas explicitamente. As APIs REST do OData normalmente lidam com isso usando $metadatadefinição de Power Query e o método Power Query lida automaticamente com a análise dessa informação e aplica-as aos dados retornados de uma OData.Feed origem OData.

Muitas APIs REST não têm uma maneira de determinar programaticamente seu esquema. Nesses casos, você precisará incluir uma definição de esquema em seu conector.

Abordagem simples em código

A abordagem mais simples é codificar uma definição de esquema em seu conector. Isso é suficiente para a maioria dos casos de uso.

Em geral, a imposição de um esquema nos dados retornados pelo conector tem vários benefícios, como:

  • Definir os tipos de dados corretos.
  • Remover colunas que não precisam ser mostradas aos usuários finais (como IDs internas ou informações de estado).
  • Garantir que cada página de dados tenha a mesma forma adicionando quaisquer colunas que possam estar ausentes de uma resposta (APIs REST geralmente indicam que os campos devem ser nulos omitindo-os inteiramente).

Exibindo o esquema existente com Table.Schema

Considere o código a seguir que retorna uma tabela simples do serviço de exemplo TripPin OData:

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

Observação

TripPin é uma fonte OData, portanto, na verdade, faria mais sentido simplesmente usar a manipulação OData.Feed automática de esquema da função. Neste exemplo, você tratará a origem como uma API REST típica e o utilizará para demonstrar a técnica de hardcodin de um Web.Contents esquema manualmente.

Esta tabela é o resultado:

Tabela de dados da TripPin Airline.

Você pode usar a função Table.Schema útil para verificar o tipo de dados das colunas:

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

Resultado de Table.Schema aplicado aos dados da TripPin Airline.

AirlineCode e Name são do any tipo . Table.Schema retorna muitos metadados sobre as colunas em uma tabela, incluindo nomes, posições, informações de tipo e muitas propriedades avançadas, como Precision, Scale e MaxLength. Por enquanto, você só deve se preocupar com o tipo atribuído ( ), o tipo primitivo ( ) e se o valor TypeName da coluna pode ser Kind nulo ( IsNullable ).

Definindo uma tabela de esquema simples

Sua tabela de esquema será composta por duas colunas:

Coluna Detalhes
Nome O nome da coluna. Isso deve corresponder ao nome nos resultados retornados pelo serviço.
Tipo O tipo de dados M que você vai definir. Pode ser um tipo primitivo (texto, número, datetime e assim por diante) ou um tipo atribuído (Int64.Type, Conversor de Moedas. Digite e assim por diante).

A tabela de esquema em código para a tabela definirá suas Airlines AirlineCode Name colunas e como text e terá esta aparência:

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

Ao analisar alguns dos outros pontos de extremidade, considere as seguintes tabelas de esquema:

A Airports tabela tem quatro campos que você deseja manter (incluindo um do tipo record ):

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

A People tabela tem sete campos, incluindo s ( , ), uma coluna que pode ser anulada ( ) e uma coluna com um tipo list Emails AddressInfo Gender atribuído ( 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}
    })

Você pode colocar todas essas tabelas em uma única tabela de esquema SchemaTable mestre:

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

Tabela de esquemas.

A função auxiliar SchemaTransformTable

A SchemaTransformTable função auxiliar descrita abaixo será usada para impor esquemas em seus dados. Ele usa os seguintes parâmetros:

Parâmetro Tipo Descrição
tabela tabela A tabela de dados na que você deseja impor seu esquema.
esquema tabela A tabela de esquema da qual ler informações de coluna, com o seguinte tipo: type table [Name = text, Type = type] .
enforceSchema número (opcional) Uma enum que controla o comportamento da função.
O valor padrão ( ) garante que a tabela de saída corresponderá à tabela de esquema fornecida adicionando colunas ausentes e removendo EnforceSchema.Strict = 1 colunas extras.
A EnforceSchema.IgnoreExtraColumns = 2 opção pode ser usada para preservar colunas extras no resultado.
Quando EnforceSchema.IgnoreMissingColumns = 3 for usado, as colunas ausentes e as colunas extras serão ignoradas.

A lógica para essa função é semelhante a esta:

  1. Determine se há colunas ausentes da tabela de origem.
  2. Determine se há colunas extras.
  3. Ignore colunas estruturadas (do list tipo , e ) e record table colunas definidas para o tipo any .
  4. Use Table.TransformColumnTypes para definir cada tipo de coluna.
  5. Reordene colunas com base na ordem em que aparecem na tabela de esquema.
  6. De definir o tipo na própria tabela usando Value.ReplaceType .

Observação

A última etapa para definir o tipo de tabela removerá a necessidade da interface do usuário do Power Query de inferir informações de tipo ao exibir os resultados no editor de consultas, o que às vezes pode resultar em uma chamada dupla para a API.

Juntando tudo

No contexto maior de uma extensão completa, a manipulação de esquema ocorrerá quando uma tabela for retornada da API. Normalmente, essa funcionalidade ocorre no nível mais baixo da função de paging (se houver), com informações de entidade passadas de uma tabela de navegação.

Como grande parte da implementação de tabelas de paging e navegação é específica do contexto, o exemplo completo de implementação de um mecanismo de manipulação de esquema em código não será mostrado aqui. Este exemplo de TripPin demonstra a aparência de uma solução de ponta a ponta.

Abordagem sofisticada

A implementação em código discutida acima faz um bom trabalho de garantir que os esquemas permaneçam consistentes para ressos JSON simples, mas está limitado à análise do primeiro nível da resposta. Os conjuntos de dados profundamente aninhados se beneficiariam da abordagem a seguir, que aproveita os tipos M.

Aqui está uma atualização rápida sobre tipos na linguagem M da Especificação de Linguagem:

Um valor de tipo é um valor que classifica outros valores. Um valor classificado por um tipo obedece a esse tipo. O sistema de tipos de M é composto pelas seguintes categorias de tipos:

  • Tipos primitivos, que classificam valores primitivos ( , , , , , , , , , , , , ) e também incluem vários tipos binary date datetime datetimezone duration list logical null number record text time type abstratos ( function , , table e any none ).
  • Tipos de registro, que classificam valores de registro com base em nomes de campo e tipos de valor.
  • Tipos de lista, que classificam listas usando um único tipo de base de item.
  • Tipos de função, que classificam valores de função com base nos tipos de seus parâmetros e valores de retorno.
  • Tipos de tabela, que classificam valores de tabela com base em nomes de coluna, tipos de coluna e chaves.
  • Tipos anuáveis, que classificam o valor nulo, além de todos os valores classificados por um tipo base.
  • Tipos de tipo, que classificam valores que são tipos.

Usando asaída JSON bruta que você recebe (e/ou procurando as definições no $metadata do serviço), você pode definir os seguintes tipos de registro para representar tipos complexos OData:

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

Observe como LocationType referencia e para representar suas CityType LocType colunas estruturadas.

Para as entidades de nível superior que você deseja que representem como Tabelas, você pode definir tipos de tabela:

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

Em seguida, você pode atualizar sua variável (que pode ser usada como uma tabela de busca para mapeamentos de entidade para tipo) para SchemaTable usar essas novas definições de tipo:

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

Você pode contar com uma função comum ( ) para impor um esquema em seus dados, assim como você Table.ChangeType SchemaTransformTable usou no exercício anterior. Ao contrário de , o aceita um tipo de tabela M real como um argumento e aplicará o esquema SchemaTransformTable Table.ChangeType recursivamente para todos os tipos aninhados. Sua assinatura é:

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

Observação

Para flexibilidade, a função pode ser usada em tabelas, bem como listas de registros (que é como as tabelas são representadas em um documento JSON).

Em seguida, você precisará atualizar o código do conector para alterar o parâmetro de um para schema um e adicionar uma chamada a table type Table.ChangeType . Novamente, os detalhes para fazer isso são muito específicos da implementação e, portanto, não vale a pena entrar em detalhes aqui. Este exemplo de conector tripPin estendido demonstra uma solução de ponta a ponta que implementa essa abordagem mais sofisticada para lidar com o esquema.