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

APPLIES TO: Azure Data Factory Azure Synapse Analytics

A estruturação de dados em Azure Data Factory permite que você faça a preparação de dados agile sem código e a estruturação em escala de nuvem traduzindo scripts Power Query M em Fluxo de Dados script. O ADF integra-se ao Power Query Online e disponibiliza Power Query M funções para a estruturação de dados por meio da execução do Spark usando a infraestrutura do Spark de fluxo de dados.

Atualmente, nem todas as funções Power Query M têm suporte 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.

Veja abaixo uma lista de funções de Power Query M com suporte.

Gerenciamento de colunas

• Seleção: Table.SelectColumns
• Remoção: Table.RemoveColumns
• Renomeação: Table.RenameColumns, Table.PrefixColumns, Table.TransformColumnNames
• Reordenação: Table.ReorderColumns

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.

• Aritmética numérica
• Concatenação de texto
• Aritmética de data e hora (operadores aritméticos, Date.AddDays, Date.AddMonths, Date.AddQuarters, Date.AddWeeks, Date.AddYears)
• As durações podem ser usadas para aritmética de data e hora, mas devem ser transformadas em outro tipo antes de serem gravadas em um coletor (operadores aritméticos, #duration, Duration.Days, Duration.Hours, Duration.Minutes, Duration.Seconds, Duration.TotalDays, Duration.TotalHours, Duration.TotalMinutes, Duration.TotalSeconds)
• A maioria das funções numéricas padrão, científicas e trigonométricas (todas as funções em Operações, Arredondamento e Trigonometria, exceto Number.Factorial, Number.Permutations, e Number.Combinations)
• Substituição (Replacer.ReplaceText, Replacer.ReplaceValue, Text.Replace, Text.Remove)
• Extração de texto posicional (Text.PositionOf, Text.Length, Text.Start, Text.End, Text.Middle, Text.ReplaceRange, Text.RemoveRange)
• Formatação de texto básica (Text.Lower, Text.Upper, Text.Trim/Start/End, Text.PadStart/End, Text.Reverse)
• Funções de data/hora (Date.Day, Date.Month, Date.YearTime.Hour, Time.Minute, Time.Second, Date.DayOfWeek, Date.DayOfYear, Date.DaysInMonth)
• Se forem expressões (mas as ramificações devem ter tipos correspondentes)
• Filtros de linha como uma coluna lógica
• Constantes de número, texto, lógica, data e hora

Mesclar/unir tabelas

• 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.

• Deve ser usado com uma função de agregação
• Funções de agregação com suporte: List.Sum, List.Count, List.Average, List.Min, List.Max, List.StandardDeviation, List.First, List.Last

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

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

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

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

• 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 Editor Avançado opção 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 Power Query ADF, siga estes conjuntos para definir o formato.

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 Editor Avançado e altere TransformColumnTypes para TransformColumns. Especifique o formato e a cultura com base nos dados de entrada.

#"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 um Power Query de estrutura de dados no ADF.