Práticas recomendadas de fluxo de linguagem do Power Query

Neste tutorial, você aprenderá sobre o fluxo de linguagem do Power Query e algumas de suas práticas recomendadas. Você também aprenderá como transformar seus dados facilmente para obter os melhores resultados possíveis. As transformações de dados otimizarão seu relatório e o tornarão atraente.

Índice

• Definir tipos de dados sem fluxo de linguagem do Power Query
  • Definir tipos de dados usando a guia Transformar
  • Definir tipos de dados usando o cabeçalho da coluna
  • Definir tipos de dados usando ícones de coluna
• Definir tipos de dados com fluxo de linguagem do Power Query
  • Remover colunas desnecessárias
  • Atribuir tipos de dados apropriados a colunas e limitar linhas
  • Consolidar etapas redundantes no fluxo de linguagem do Power Query
• Organize consultas para um melhor fluxo de linguagem de consulta de energia
• Resumo do fluxo de linguagem do Power Query
• Conclusão

Definir tipos de dados sem fluxo de linguagem do Power Query

As transformações mínimas que você sempre deve executar são renomear e definir tipos de dados para suas colunas.

Os tipos de dados são usados ​​para classificar valores em M . Um ícone ao lado do nome da coluna mostra o tipo de dados atribuído a um campo. Nesta tabela de amostra, você pode ver um ícone ABC123 ao lado dos nomes das colunas. Esse ícone representa o tipo de dados Qualquer que indica que um tipo de dados não foi atribuído a essa coluna. 

Fora dos livros, a detecção automática de tipo está habilitada. Isso gera etapas de Tipo alterado automaticamente em suas consultas. Mesmo se estiver habilitado, certifique-se sempre de que o Power Query adivinhe os tipos de dados corretamente para cada uma das colunas em todas as suas tabelas.

Para ativar ou desativar essa configuração, siga estas etapas.

Na opção Global , você pode selecionar uma das três opções de detecção de tipo disponíveis para você.

Na opção Arquivo atual, você pode alternar as opções para Detecção de tipo, dependendo da sua configuração global.

Definir tipos de dados usando a guia Transformar

A interface do usuário oferece várias maneiras de definir tipos de dados para suas colunas. Na seção Qualquer coluna da guia Transformar, você encontrará o botão Detectar tipo de dados .

Se você selecionar todas as colunas e clicar nesse botão, ele definirá automaticamente os tipos de dados de todas as colunas. No entanto, como ele verifica apenas as 200 primeiras linhas, você ainda precisa validar se o Power Query tem o tipo de dados correto.

Se você selecionar uma coluna de sua tabela, poderá ver seu tipo de dados na seção Qualquer coluna.

Se você clicar no botão suspenso e selecionar Texto, o ícone na coluna selecionada mudará de ABC123 para apenas ABC .

Você verá uma etapa de Tipo alterado no painel Etapas aplicadas.

Na guia Início, você também encontrará a opção de definir o tipo de dados.

Definir tipos de dados usando o cabeçalho da coluna

Outra maneira é clicar com o botão direito do mouse em um cabeçalho e selecionar Alterar tipo . A partir daí, você pode escolher o tipo de dados apropriado para suas colunas.

Definir tipos de dados usando ícones de coluna

A maneira mais comum de definir tipos de dados é clicar no ícone ao lado do nome da coluna. Ele mostrará todos os tipos de dados disponíveis.

Na tabela de amostra, defina o tipo de dados da coluna OrderDate como Date .

Dependendo do tipo de dados da coluna selecionada, o Power Query fornece um conjunto de transformações e opções que se aplicam a esse tipo de dados específico na guia Transformar , na guia Adicionar Coluna e na seção Filtro Inteligente .

Portanto, se você selecionar uma coluna de data e for para a guia Transformar, verá as opções no botão Data.

No entanto, se você selecionar uma coluna de texto, as opções no botão Data não estarão mais disponíveis.

Para filtragem inteligente, as colunas de texto têm filtros de texto e as colunas de data têm filtros de data.

Para a próxima coluna, altere o nome da coluna para Chave do cliente e o tipo de dados para Número inteiro .

Em seguida, altere o tipo de dados das colunas Canal, Código de Moeda e Código do Armazém para Texto.

Em seguida, altere o nome do Delivery Region Index para Delivery Region Key e seu tipo de dados para Whole Number . Em seguida, faça o mesmo para as colunas Índice de descrição do produto e Quantidade do pedido .

Por fim, para as colunas Unit Price, Line Total e Unit Cost, defina o tipo de dados como Fixed Decimal Number .

Definir tipos de dados com fluxo de linguagem do Power Query

Devido às alterações nas colunas, muitas etapas foram criadas no painel Etapas Aplicadas. O problema é que, devido à execução do mesmo tipo de transformação várias vezes, a consulta da tabela de amostra se torna ineficiente. Isso é algo que você deve evitar.

Para tornar sua consulta eficiente, tente criar uma única etapa aplicando essa transformação específica a todas as suas colunas antes de criar outra etapa.

Duplique a consulta da tabela de exemplo e renomeie-a como Best Practice . No painel Applied Steps, clique com o botão direito do mouse na primeira transformação executada na consulta anterior e selecione Delete Until End . Em seguida, confirme a exclusão da etapa na caixa de diálogo Excluir etapa .

Além disso, aqui estão algumas práticas recomendadas. A primeira coisa é criar parâmetros para conter a localização da fonte de dados. Isso facilita a correção de problemas quando um nome de arquivo é alterado.

Para criar parâmetros, clique em Gerenciar parâmetros na guia Início e selecione Novo parâmetro .

Outra maneira é clicar com o botão direito do mouse no painel Consulta e selecionar Novo parâmetro .

Depois disso, uma caixa de diálogo Gerenciar parâmetros aparecerá. Nomeie o parâmetro FileLocation e defina o Tipo como Texto. Para os valores sugeridos, defina-o como lista de valores para que você possa colar sua string e alterar ou adicionar vários locais que você pode alternar entre eles.

Em seguida, vá para o File Explorer e selecione seu arquivo. Copie o caminho e cole os parâmetros. Feito isso, pressione OK .

Volte para a consulta Best Practice e clique na etapa Source no painel Applied Steps. Em seguida, altere o caminho do arquivo codificado na barra de fórmulas com FileLocation .

Remover colunas desnecessárias

Remova todas as colunas desnecessárias para economizar espaço e melhorar o desempenho. Traga apenas os dados necessários, pois adicionar tabelas e colunas é muito mais fácil do que removê-los.

A maneira mais fácil de fazer isso é por meio da etapa Escolher colunas na guia Início. Se você clicar nesse botão, aparecerá uma caixa de diálogo que permite escolher as colunas a serem mantidas. Você deve projetar e moldar suas tabelas com uma finalidade específica para melhor atender a análise que você vai realizar.

A partir daí, desmarque as colunas que você não precisa em sua tabela. Para esta tabela de amostra, a cardinalidade da coluna OrderNumber é alta. É melhor desmarcar essa coluna porque ela afeta o tamanho do arquivo e o desempenho geral.

Se você não precisar de dados de localização para a análise da consulta de amostra, também é melhor desmarcar o Índice da região de entrega . Por fim, como a tabela já possui preço unitário e quantidade, a coluna Total da Linha não é necessária.

Depois de desmarcar as colunas, pressione OK . Se quiser alterar a seleção da coluna, basta limpar o ícone de engrenagem ao lado do nome da etapa no painel Etapas aplicadas.

Atribuir tipos de dados apropriados a colunas e limitar linhas

Em seguida, atribua tipos de dados para todas as colunas. Selecione todas as colunas e clique em Detectar tipo de dados na guia Transformar.

Detectar tipo de dados detecta automaticamente os tipos de dados de colunas com base na verificação das 200 primeiras linhas. Portanto, verifique e certifique-se de que o Power Query define os tipos de dados corretos.

É uma prática recomendada limitar o número de linhas. Se o seu ano fiscal começa em 1º de julho, você pode omitir os dados de junho ou configurar um parâmetro.

No entanto, há um problema se você quiser alterar um valor de parâmetro no serviço do LuckyTemplates . Depois de publicar seu relatório, seus valores de parâmetro precisam ser do tipo Texto ou Decimal.

Para lidar com isso, crie um filtro nos dados clicando no botão suspenso na coluna OrderDate. Em seguida, clique em Filtros de data e selecione Após .

Na caixa de diálogo, altere o primeiro parâmetro para é posterior ou igual a e, em seguida, insira a data. Neste exemplo, a data inserida foi 1º de julho de 2014.

Depois de pressionar OK , o filtro será aplicado em sua tabela. Em seguida, crie um parâmetro e nomeie-o como DatesFrom . Defina o Tipo como Texto e insira a data no parâmetro Valor atual.

Agora, volte para a consulta Best Practice e substitua #date intrínseco por Date.From(DatesFrom) .

Clicar no sinal de verificação sem a função Date.From retornará um erro. Isso ocorre porque DatesFrom é um tipo de texto enquanto o campo tem um tipo de data. A função Date.From converte Texto em Data.

Renomeie todas as colunas que não ficarão ocultas no modelo de dados. O nome precisa ser conciso, autodescritivo e fácil de usar. Lembre-se de que você está projetando um modelo de dados para pessoas que usarão seu relatório.

Consolidar etapas redundantes no fluxo de linguagem do Power Query

A próxima coisa a fazer é consolidar as etapas redundantes (como renomear, remover e alterar os tipos de dados da coluna). Além disso, etapas como reordenar colunas são coisas às quais você deve prestar atenção ao procurar redundâncias.

As tabelas que você carrega no modelo de dados nunca são exibidas em seu relatório. Isso torna a ordem das colunas irrelevante.

Outra prática recomendada é renomear suas etapas no painel Etapas aplicadas. Os nomes das etapas são autodescritivos e são usados ​​como variáveis ​​no código M.

Nomes que contêm espaços ou caracteres especiais são escritos usando a notação entre aspas. Isso significa que os nomes estão entre aspas duplas e têm o sinal de cerquilha ou libra na frente deles, o que dificulta a leitura do código M. Você pode omitir os espaços ou colocar um sublinhado entre eles.

Adicionar detalhes de documentação adicionais colocando comentários na janela do Editor Avançado também é uma prática recomendada no Power Query. Você também pode fazer isso na Descrição da propriedade da etapa . Elas são mostradas como anotações de dica de ferramenta quando você passa o mouse sobre uma etapa com um ponto de exclamação no painel Etapas aplicadas.

Saber por que você fez uma determinada escolha no desenvolvimento inicial é extremamente útil quando você precisa revisitar um arquivo depois de algum tempo. Para adicionar detalhes da documentação, clique com o botão direito do mouse em uma etapa no painel Etapas aplicadas e selecione Propriedades .

Uma caixa de diálogo Step Properties aparecerá onde você pode escrever o motivo da filtragem ou transformação.

Organize consultas para um melhor fluxo de linguagem de consulta de energia

Uma das práticas recomendadas mais comuns no Power Query é organizar suas consultas . Crie pastas para parâmetros, funções, consultas de preparação e consultas que serão carregadas no modelo de dados. Neste exemplo, selecione as consultas FileLocation e DatesFrom e clique com o botão direito nelas. Em seguida, selecione Mover para grupo e clique em Novo grupo .

Em seguida, adicione um nome para as consultas selecionadas e pressione OK .

Depois de agrupar suas consultas, seu painel de consulta ficará assim.

Para todas as suas consultas de preparação, certifique-se de desabilitar a carga desmarcando Habilitar carga.

Outra coisa a discutir neste tutorial é o fluxo de linguagem. Cada uma das etapas no painel Etapas aplicadas transforma um valor que você pode ver ao clicar nelas.

Os dados iniciais vieram e começaram na etapa de Navegação, e as colunas foram selecionadas. Em seguida, os tipos de dados foram alterados e um intervalo de datas foi definido. As colunas também foram renomeadas.

Todas as etapas retornam um valor de tipo de tabela. Se você abrir a janela do Editor avançado, verá uma expressão let e uma cláusula in . Entre eles, há uma lista de passos ou nomes de variáveis ​​com expressões atribuídas a eles.

A consulta retorna o que segue a cláusula in que se refere à etapa final em sua lista de variáveis. O mecanismo M seguirá a cadeia de dependências de volta da cláusula in para eliminar tudo o que for desnecessário e enviar as transformações de volta à origem, se possível.

Resumo do fluxo de linguagem do Power Query

Se você observar a fórmula, poderá ver o fluxo das etapas realizadas na consulta. Você também verá as funções usadas pela interface do usuário em cada etapa.

O primeiro passo que foi feito na consulta foi a seleção da coluna. Quando a etapa foi executada usando a interface do usuário, a função Table.SelectColumns foi chamada. Como primeiro parâmetro, tomou uma tabela que referenciava o nome da variável do passo anterior. Em seguida, listou todos os nomes de coluna selecionados.

A segunda etapa transformou os tipos de coluna chamando a função Table.TransformColumnTypes . Seu primeiro parâmetro chamou a saída da etapa anterior. Em seguida, listou um conjunto de listas de transformação.

A terceira etapa define um filtro de intervalo de datas usando a função Table.SelectRows . Ele levou uma consulta de tipo de tabela como seu primeiro argumento. Neste exemplo, ele referenciou a saída da etapa Change Type.

A última etapa renomeou as colunas usando a função Table.RenameColumns . A saída da etapa anterior foi usada como seu primeiro argumento. Em seguida, listou um conjunto de listas de renomeação.

Todas as funções aplicadas por meio da interface do usuário começam com a palavra Table . Todos eles pegaram um valor de tabela como primeiro parâmetro e depois transformaram esse valor.

Embora o código pareça sequencial porque cada etapa se refere à etapa anterior, a sequência não é necessária. Se você mover uma etapa, a consulta ainda será executada porque o mecanismo M sempre segue a cadeia de dependência.

Ter a interface do usuário é conveniente, mas sempre assumirá que você deseja transformar os resultados da transformação anterior. Na maioria dos casos, isso provavelmente será verdade e, caso não seja, você terá que atualizar manualmente o valor referenciado no código.

Tipos de dados e conectores do Power Query
Conjuntos de dados do LuckyTemplates: tipos e convenções de nomenclatura
Práticas recomendadas de carregamento e transformação de dados

Conclusão

A transformação de dados é necessária para manter seus dados agrupados e organizados. Isso torna o desenvolvimento de dados mais rápido porque você pode facilmente rastrear problemas no fluxo de linguagem do Power Query e modificar alterações em seu relatório.

melissa