M Code In Power Query Colunas Personalizadas | LuckyTemplates

No blog de hoje, apresentarei a solução para o , incluindo uma estrutura simples e generalizável para incorporar código M em uma coluna personalizada do Power Query. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Ser capaz de criar colunas no Power Query usando código M aumenta sua capacidade de resolver problemas mais complexos.

Antes de entrarmos na solução, vamos revisar a natureza do problema. Forneci um grande conjunto de dados de Kaggle de 93 anos de dados do Oscar. Queríamos selecionar a melhor atriz, as vencedoras e, principalmente, as mulheres que ganharam mais de um prêmio de melhor atriz e, em seguida, visualizar, usando a linha do tempo da Queryon, o ano em que ganharam seu segundo prêmio.

Eu tinha vários objetivos ao montar esse problema. Em que queremos que o problema se concentre e quais sejam os objetivos principais?

E neste, havia três deles.

A primeira foi aprender ou desenvolver alguma prática com a construção Group By e All Rows . É algo em que tenho me concentrado muito porque resolve uma ampla gama de problemas de maneira bastante simples e, muitas vezes, elimina a necessidade de algum DAX muito complexo.

O segundo objetivo é especialmente para pessoas que não estavam acostumadas a trabalhar em M. Eu queria criar um problema que desenvolvesse algum conforto ao escrever código M básico . Eu queria forçar esse modo de pensar sobre como é desenvolver código M básico e o processo de fazer isso.

De acordo com um capítulo do livro de Gil Raviv sobre Power Query, cerca de 40% dos problemas no Power Query podem ser resolvidos apenas por meio da interface do usuário. Mas isso, com a adição de algum código M básico e a capacidade de criar colunas personalizadas, não é tão difícil. Você pode aumentar essa porcentagem em mais de 80%. Você pode dobrar suas capacidades de resolução de problemas com algumas habilidades simples e fundamentais.

A terceira coisa que quero fazer é apresentar o visual personalizado da linha do tempo do Queryon para aqueles que não estão familiarizados com ele. É um que eu realmente aprendi a amar. Eu o usei em várias instâncias diferentes e acho que ele fornece uma maneira realmente única de visualizar dados.

Índice

• Abordagem da solução para o problema da semana nº 9
• Usando Group By em Power Query
• Incorporando Tabela.AddIndexColumn Código M
• Visualizando usando o visual personalizado da linha do tempo do Queryon
• Conclusão

Abordagem da solução para o problema da semana nº 9

Ao pensar sobre esse problema, eu queria fazer um experimento mental. E se, em vez de um arquivo CSV, eu apenas desse a você 93 tiras de papel (uma com a vencedora de Melhor Atriz de cada ano), como seria a solução?

Sua solução provavelmente se pareceria com o que tenho abaixo.

Acho que todos teriam uma solução relativamente semelhante a isso. Depois de obter essa solução conceitual, é apenas uma questão de descobrir como fazer o Power Query executar cada uma dessas etapas.

E assim, vou apenas tomar isso como um esboço geral e, em seguida, passar por como eles operacionalizam essa solução conceitual em algo no Power Query. A abordagem que vamos usar é o que Melissa, nossa guru residente do Power Query, chama de aprender pelo Google.

Agora, se voltarmos ao nosso modelo conceitual, as duas primeiras etapas incluem classificar os papéis em grupos por atriz e descartar qualquer grupo com apenas um papel. Então, queremos fazer uma contagem. Se não soubermos como fazer isso, podemos entrar no Google e tentar pesquisar um termo. Neste exemplo, tentei “group in power query”. Com isso, podemos encontrar tudo o que precisamos saber sobre como fazer esse agrupamento.

Usando Group By em Power Query

Em seguida, podemos pular para o Power Query e começar a operacionalizar nossa solução. O que eu fiz é que, nesta tabela do Oscar, acabei de passar e puxei o arquivo CSV e filtrei para os vencedores de Melhor Atriz de cada ano. Portanto, temos apenas as 93 linhas de Nome e Ano do filme.

Se entrarmos no Power Query e formos para essa tabela, a primeira coisa que queremos fazer é esse Group By .

Eu sempre gostei de ir para Avançado apenas porque ele fornece uma interface um pouco melhor, mesmo que o que estamos fazendo seja bastante direto.

Queremos agrupar por Name , então vamos ter uma coluna, AllData , e isso será apenas o nosso All Rows. Em vez de retornar apenas a agregação, ele retornará a agregação, mas com todas as linhas que queremos ainda intactas.

Teremos outra coluna chamada Count e a queremos quando vamos Count Rows . Queremos encontrar o número de deslizamentos em cada pilha. Em seguida, clicamos em OK.

Podemos então clicar neste espaço em branco na tabela e obter apenas um deslizamento.

Mas então, para aqueles que têm várias contagens, você verá que obtemos todos os registros.

Em seguida, queremos filtrar os (1). Então, estamos basicamente destruindo todas aquelas pilhas que têm apenas um nome. Selecione 1 e clique em OK.

E agora estamos reduzidos aos 14 vencedores de vários prêmios. E agora estamos reduzidos aos 14 vencedores de vários prêmios. Se pegarmos e expandirmos isso, podemos retirar Name porque já temos Name aqui e apenas manter year_film .

Agora estamos reduzidos a 30 linhas porque algumas delas terão várias vitórias. E podemos até nos livrar da coluna Count porque já descartamos os 1s com apenas um.

Agora vamos para a próxima fase do nosso modelo conceitual, que é classificar os boletos em cada grupo por data ascendente e, em seguida, escolher o segundo dia de ocorrência dentro de cada grupo.

Classificar é fácil. Podemos voltar ao Power Query e fazer uma Classificação Ascendente por Atriz e depois por Data.

E mostrará a você um e um dois.

Então, estamos bem classificados e agora queremos ter um índice ou uma classificação que tenha para cada um e, em seguida, poder pegar o segundo em cada pilha.

Incorporando Tabela.AddIndexColumn Código M

E agora estamos de volta à nossa abordagem “Aprendido pelo Google”, onde podemos apenas pesquisar classificação ou índice, mas também podemos ir para o Guia de Referência da Microsoft . Eu criei uma ferramenta externa que eu uso o tempo todo, que apenas leva você do LuckyTemplates para o Guia de Referência da Microsoft.

No Guia de Referência da Microsoft, tudo está agrupado por função e tem uma caixa de filtro onde você pode pesquisar o que precisar. Nesse caso, digitamos Rank ou Índice na caixa de filtro. Não nos dá nada para Rank, mas para Index, temos esta Table.AddIndexColumn , que é relevante e aplicável ao que estamos procurando. Ele também fornece alguns exemplos de como usar essa função.

Então vamos entrar no Power Query e reagrupar nossa tabela por Atriz.

Com isso, obtemos o grupo dos 14 vencedores por Tabela. E se entrarmos em qualquer uma dessas tabelas, digamos, Frances McDormand, podemos ver que a tabela está classificada por Data.

E então, aqui está o grande passo, que é adicionar uma coluna personalizada do Power Query e inserir nosso código M. Teremos o Table.AddIndexColumn , então adicionamos o campo AllData . Chamaremos nossa nova coluna (como texto) aqui de Index , e iniciaremos nosso Index em um (1) e o incrementaremos em um (1).

E nós obtemos este índice perfeito aqui.

Agora, podemos excluir nossa coluna AllData aqui, já que tudo foi coletado no Custom . Em seguida, podemos apenas expandir a coluna Custom, retirar Name, porque já temos Name aqui também. Com isso, o que temos é aquele índice numerado para cada um.

Por fim, podemos fazer o que fizemos no Count, que é filtrar todos os que estão na segunda vitória.

E lá vamos nós. Aí está a nossa solução.

Visualizando usando o visual personalizado da linha do tempo do Queryon

Há algumas outras coisas que queremos fazer aqui, todas baseadas na implementação do Queryon Visual . Faremos uma mesclagem e apenas adicionaremos o URL da imagem.

Portanto, mesclaremos as consultas, abriremos a tabela Atrizes e mesclaremos Name . Temos de 14 a 14 linhas correspondentes, o que é bom.

E então, na coluna Atrizes, vamos apenas expandir e expandir para o URL.

E agora temos esta coluna com as URLs.

Há uma outra coisa que vem com o conhecimento da linha do tempo do Queryon. O Queryon funciona melhor quando lida com datas completas em vez de anos . E assim, isso é algo que vem apenas com a experimentação, mas podemos pegar a coluna Year e criar uma coluna de exemplos . O que queremos fazer aqui é apenas transformar os anos em datas.

Isso vai melhorar a formatação. Isso é algo que eu não esperaria que você soubesse logo de cara, mas vem com a prática com o Queryon. Clicamos em OK e isso entra como um valor de data. Podemos excluir a tabela de índice porque não precisamos mais dela e podemos alterar o nome da coluna year_film para Year .

Vamos agora pular para o aspecto de visualização disso e quero destacar a entrada de um dos membros do LuckyTemplates, José, que realmente fez um ótimo trabalho na visualização.

Voltando ao Power Query para a visualização, vale a pena mencionar que a Microsoft implementou recentemente algumas alterações na forma como certifica visuais personalizados . Como parte dessa certificação, eles restringiram o uso de imagens de URL externas.

Se você realmente usar a versão do Queryon Timeline que está no App Source (versão 1.05), não conseguirá extrair essas imagens de URL. E assim, agora você tem a opção de usar este 1.04, mantendo as imagens de URL, onde eles têm em seu site uma maneira de incorporar imagens binárias 64 diretamente no arquivo PBIX.

Mencionei a alteração desse ano para um campo de data, portanto, precisamos conectá-lo ao nosso modelo de dados.

Vamos apenas alterá-lo de um para um para um para muitos e direção única no filtro cruzado e, em seguida, tornar esse relacionamento ativo .

Em seguida, para o campo URL da imagem, precisamos garantir que, na categoria Dados, não seja exibido como Sem categoria, mas como URL da imagem .

Agora que temos tudo bem preparado, basta arrastar e soltar os campos Name , Date e URL . O resto se torna uma limpeza realmente direta nas opções de formato.

Tabela do Power Query: Como mesclar colunas dinamicamente
Como mesclar consultas no LuckyTemplates
Extrair valores de registros e listas no Power Query

Conclusão

Espero ter deixado você animado para adicionar o código M ao seu repertório. Mostrei a você como configurar um modelo conceitual e implementar a abordagem Aprenda pesquisando no Google. Acompanhei você pelas etapas de incorporação do código M em uma coluna personalizada e visualização dos resultados usando o Queryon Timeline.

Espero que você tenha achado isso útil. Assista ao vídeo completo abaixo para obter mais detalhes sobre o tutorial e confira os links abaixo para mais conteúdo relacionado.

Tudo de bom!