Funções de transformação do Power Query para estruturação de dados

  • Artigo

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Dica

Experimente o Data Factory no Microsoft Fabric, uma solução de análise tudo-em-um para empresas. O Microsoft Fabric abrange desde movimentação de dados até ciência de dados, análise em tempo real, business intelligence e relatórios. Saiba como iniciar uma avaliação gratuita!

A estruturação de dados no Azure Data Factory permite que você faça a preparação de dados ágil sem código e estruturação em escala de nuvem, convertendo scripts do Power Query M em scripts do Fluxo de Dados. O ADF se integra com o Power Query online e disponibiliza funções do Power Query M para estruturação de dados por meio da execução do Spark usando a infraestrutura de fluxo de dados do Spark.

Atualmente, não há suporte para todas as funções M do Power Query para estruturação de dados, apesar de estarem disponíveis durante a criação. Ao criar os mash-ups, a seguinte mensagem de erro será exibida se não houver suporte para uma função:

UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out.

Abaixo está uma lista de funções M do Power Query com suporte.

Gerenciamento de colunas

Filtragem de linhas

Use a função M Table.SelectRows para filtrar as seguintes condições:

  • Igualdade e desigualdade
  • Comparações numéricas, de texto e de data (mas não DateTime)
  • Informações numéricas, como Number.IsEven/Odd
  • Texto que contém palavras específicas, usando Text.Contains, Text.StartsWith ou Text.EndsWith
  • Intervalos de datas incluindo todas as Funções de data 'IsIn')
  • Combinações dessas usando as condições “e”, “ou” ou “não”

Adicionar e transformar colunas

As seguintes funções M adicionam ou transformam colunas: Table.AddColumn, Table.TransformColumns, Table.replacevalue, Table.DuplicateColumn. Abaixo estão as funções de transformação com suporte.

Mesclar/unir tabelas

  • O Power Query gerará uma junção aninhada (Table.NestedJoin; os usuários também podem gravar manualmente Table.AddJoinColumn). Os usuários devem expandir a coluna de junção aninhada para uma junção não aninhada (Table.ExpandTableColumn, sem suporte em nenhum outro contexto).
  • A função M Table.Join pode ser gravada diretamente para evitar a necessidade de uma etapa de expansão adicional, mas o usuário deve garantir que não haja nenhum nome de coluna duplicado entre as tabelas unidas
  • Tipos de junção com suporte: Inner, LeftOuter, RightOuter, FullOuter
  • Value.Equals e Value.NullableEquals têm suporte como comparadores principais de igualdade

Agrupar por

Use Table.Group para agregar valores.

Classificação

Use Table.Sort para classificar valores.

Reduzir linhas

Manter e remover linhas superiores, manter o intervalo (funções M correspondentes, somente contagens de suporte, não condições: Table.FirstN, Table.Skip, Table.RemoveFirstN, Table.Range, Table.MinN, Table.MaxN)

Funções conhecidas sem suporte

Função Status
Table.PromoteHeaders Não há suporte. O mesmo resultado pode ser obtido com a configuração “Primeira linha como cabeçalho” no conjunto de dados.
Table.CombineColumns Esse é um cenário comum que não tem suporte direto, mas pode ser obtido com a adição de uma nova coluna que concatena duas colunas especificadas. Por exemplo, Table.AddColumn(RemoveEmailColumn, "Name", each [FirstName] & " " & [LastName])
Table.TransformColumnTypes Isso tem suporte na maioria dos casos. Os seguintes cenários não têm suporte: transformar cadeia de caracteres em tipo de moeda, transformar cadeia de caracteres em tipo de hora, transformar cadeia de caracteres em tipo de porcentagem e transformar com localidade.
Table.NestedJoin Apenas fazer uma junção resultará em um erro de validação. As colunas devem ser expandidas para que funcionem.
Table.RemoveLastN Não há suporte para a remoção de linhas inferiores.
Table.RowCount Sem suporte, mas pode ser obtido com a adição de uma coluna personalizada contendo o valor 1 e, em seguida, a agregação dessa coluna com List.Sum. Table.Group é compatível.
Tratamento de erros em nível de linha No momento, não há suporte para o tratamento de erros em nível de linha. Por exemplo, para filtrar valores não numéricos de uma coluna, uma abordagem será transformar a coluna de texto em um número. Todas as células, que não forem transformadas estarão em um estado de erro e precisarão ser filtradas. Esse cenário não é possível em M expandido.
Table.Transpose Sem suporte

Soluções alternativas de script M

SplitColumn

Vejas abaixo uma alternativa para a divisão por comprimento e por posição

  • Table.AddColumn(Source, "First characters", each Text.Start([Email], 7), type text)
  • Table.AddColumn(#"Inserted first characters", "Text range", each Text.Middle([Email], 4, 9), type text)

Essa opção pode ser acessada na opção Extrair na faixa de opções

Power Query Add Column

Table.CombineColumns

  • Table.AddColumn(RemoveEmailColumn, "Name", each [FirstName] & " " & [LastName])

Tabelas dinâmicas

  • Selecione a transformação Dinamizar no editor do PQ e selecione a coluna dinâmica

Power Query Pivot Common

  • Em seguida, selecione a coluna de valor e a função de agregação

Power Query Pivot Selector

  • Ao clicar em OK, você verá os dados no editor atualizados com os valores dinâmicos
  • Você também verá uma mensagem de aviso de que a transformação pode não ter suporte
  • Para corrigir esse aviso, expanda a lista dinâmica manualmente usando o editor PQ
  • Selecione a opção Editor Avançado na faixa de opções
  • Expanda manualmente a lista de valores dinâmicos
  • Substitua List.Distinct() pela lista de valores como este:
#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type 1", {{"genres", type text}}), {"Drama", "Horror", "Comedy", "Musical", "Documentary"}, "genres", "Rating", List.Average)
in
  #"Pivoted column"

Formatar colunas de data/hora

PARA definir o formato de data/hora ao usar o ADF do Power Query, siga estes conjuntos para definir o formato.

Power Query Change Type

  1. Selecione a coluna na interface do usuário do Power Query e escolha alterar tipo > Data/Hora
  2. Você verá uma mensagem de aviso
  3. Abra o Editor Avançado e altere TransformColumnTypes para TransformColumns. Especifique o formato e a cultura com base nos dados de entrada.

Power Query Editor

#"Changed column type 1" = Table.TransformColumns(#"Duplicated column", {{"start - Copy", each DateTime.FromText(_, [Format = "yyyy-MM-dd HH:mm:ss", Culture = "en-us"]), type datetime}})

Conteúdo relacionado

Saiba como criar uma estruturação de dados do Power Query no ADF.