Arquivos Aprenda Power Query

Contagem regressiva de dias úteis no Power BI

Ao montar um relatório, já foi questionado se poderia disponibilizar um dashboard com contagem regressiva de dias úteis do mês? Ou já precisou automatizar essa variação de dias para rastrear a evolução da meta?

Com os passos rápidos que iremos lhe disponibilizar neste post esperamos ajudá-lo com esta necessidade, deixando o seu relatório dinâmico!

Requisitos para iniciar a criação da tabela calendário

Inicialmente é necessário criar sua tabela calendário, seja ela do modo automático (CALENDARAUTO), com data pré-definidas (CALENDAR) ou através da Linguagem M (Consulta Nula no Power Pivot). Se precisar de ajuda, clique nos links de cada tipo de calendário para te ajudar!

Primeiro crie uma tabela auxiliar com as datas de feriado da sua região. Pode-se realizar através de uma tabela do EXCEL ou utilizando o API disponibilizado pelo site www.calendario.com.br. Para acessar diretamente as informações no Power BI, você pode utilizar estes dois posts como complemento sobre WEBSCRAPING. A informação do código IBGE do município está disponível neste XLSX.

Em nosso exemplo utilizaremos um calendário criado através do Power Pivot, utilizando a linguagem M e utilizaremos um repositório em .XLSX com as datas dos feriados de Belo Horizonte em 2020. É tudo o que você precisará para construir uma contagem regressiva de dias úteis em calendário no Power BI.

Construindo a tabela calendário

Primeiro precisamos preparar as tabelas bases para conseguirmos fazer a contagem regressiva de dias úteis do calendário no Power BI. Iniciaremos construindo nossa tabela de calendário com os dias úteis:

Obter Dados -> Consulta Nula -> insira a expressão abaixo (A data inicial [2020.1.1] e também a quantidade de anos (2) a ser retornado podem ser modificados de acordo com suas necessidades).

= List.Dates (#date(2020,1,1), 365 * 2, #duration(1,0,0,0))

1	= List.Dates (#date(2020,1,1), 365 * 2, #duration(1,0,0,0))

OBS: Dependendo do idioma do seu PBI, é possível que tenha que substituir a palavra FONTE, para isto, veja o nome da primeira etapa criada em ETAPAS APLICADAS em sua versão.

Depois, converta a Lista Criada em Tabela.

Em seguida, modifique a coluna para tipo DATA e insira as divisões de data conforme necessário. Para nosso caso, necessitamos inserir o Dia da Semana e o Nome do Dia:

Adicionar Coluna -> Data -> Dia -> Dia da Semana
Adicionar Coluna -> Data -> Dia -> Nome do Dia

Depois deste tratamento inicial do calendário, carregamos as informações de feriados do XLXS. Importe o repositório de feriados criados em XLSX em Nova Fonte.

Faremos a combinação das Tabelas de Feriados e Calendário através da função Mesclar:

Mantenha-se na consulta do dCalendário -> Página Inicial -> Mesclar Consultas (1) -> Mesclar Consultas (2) -> Selecione a tabela de Feriados -> Clique sobre a Data da tabela Feriado -> OK

Expandimos a consulta mesclada, selecionando apenas a coluna Feriado.

Inseriremos uma coluna condicional onde sábados, domingos e feriados são contabilizados como 0, enquanto os demais valores serão 1 (estes valores serão contabilizados para obter a quantidade de dias úteis). Para fazer a contagem regressiva de dias úteis continue seguindo os passos.

Adicionar Coluna -> Coluna Condicional

O passo seguinte é adicionar uma coluna personalizada com a data atual:

Adicionar Coluna -> Coluna Personalizada -> Inserir a fórmula DateTime.LocalNow()

Segue as linhas de códigos utilizada no processo (Consulta Avançada):

let

    Fonte = List.Dates (#date(2020,1,1), 365 * 3, #duration(1,0,0,0)),

    #"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Tipo Alterado" = Table.TransformColumnTypes(#"Convertido para Tabela",{{"Column1", type date}}),

    #"Ano Inserido" = Table.AddColumn(#"Tipo Alterado", "Ano", each Date.Year([Column1]), Int64.Type),

    #"Colunas Removidas" = Table.RemoveColumns(#"Ano Inserido",{"Ano"}),

    #"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Removidas",{{"Column1", "Data"}}),

    #"Dia da Semana Inserido" = Table.AddColumn(#"Colunas Renomeadas", "Dia da Semana", each Date.DayOfWeek([Data]), Int64.Type),

    #"Nome do Dia Inserido" = Table.AddColumn(#"Dia da Semana Inserido", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),

    #"Consultas Mescladas" = Table.NestedJoin(#"Nome do Dia Inserido", {"Data"}, Tabela1, {"Data"}, "Tabela1", JoinKind.LeftOuter),

    #"Tabela1 Expandido1" = Table.ExpandTableColumn(#"Consultas Mescladas", "Tabela1", {"Feriado"}, {"Feriado"}),

    #"Coluna Condicional Adicionada" = Table.AddColumn(#"Tabela1 Expandido1", "Personalizar", each if ... then ... else ...),

    #"Colunas Removidas1" = Table.RemoveColumns(#"Coluna Condicional Adicionada",{"Personalizar", "Nome do Dia", "Feriado"}),

    #"Nome do Dia Inserido1" = Table.AddColumn(#"Colunas Removidas1", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),

    #"Consultas Mescladas1" = Table.NestedJoin(#"Nome do Dia Inserido1", {"Data"}, Tabela1, {"Data"}, "Tabela1", JoinKind.LeftOuter),

    #"Tabela1 Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas1", "Tabela1", {"Feriado"}, {"Feriado"}),

    #"Coluna Condicional Adicionada1" = Table.AddColumn(#"Tabela1 Expandido", "Dias Úteis", each if [Nome do Dia] = "sábado" then 0 else if [Nome do Dia] = "domingo" then 0 else if [Feriado] = 0 then 0 else 1),

    #"Personalização Adicionada" = Table.AddColumn(#"Coluna Condicional Adicionada1", "Hoje", each DateTime.LocalNow()),

    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Personalização Adicionada",{{"Hoje", type date}, {"Dias Úteis", Int64.Type}})

in

    #"Tipo Alterado2"

let

Fonte = List.Dates (#date(2020,1,1), 365 * 3, #duration(1,0,0,0)),

#"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Tipo Alterado" = Table.TransformColumnTypes(#"Convertido para Tabela",{{"Column1", type date}}),

#"Ano Inserido" = Table.AddColumn(#"Tipo Alterado", "Ano", each Date.Year([Column1]), Int64.Type),

#"Colunas Removidas" = Table.RemoveColumns(#"Ano Inserido",{"Ano"}),

#"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Removidas",{{"Column1", "Data"}}),

#"Dia da Semana Inserido" = Table.AddColumn(#"Colunas Renomeadas", "Dia da Semana", each Date.DayOfWeek([Data]), Int64.Type),

#"Nome do Dia Inserido" = Table.AddColumn(#"Dia da Semana Inserido", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),

#"Consultas Mescladas" = Table.NestedJoin(#"Nome do Dia Inserido", {"Data"}, Tabela1, {"Data"}, "Tabela1", JoinKind.LeftOuter),

#"Tabela1 Expandido1" = Table.ExpandTableColumn(#"Consultas Mescladas", "Tabela1", {"Feriado"}, {"Feriado"}),

#"Coluna Condicional Adicionada" = Table.AddColumn(#"Tabela1 Expandido1", "Personalizar", each if ... then ... else ...),

#"Colunas Removidas1" = Table.RemoveColumns(#"Coluna Condicional Adicionada",{"Personalizar", "Nome do Dia", "Feriado"}),

#"Nome do Dia Inserido1" = Table.AddColumn(#"Colunas Removidas1", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),

#"Consultas Mescladas1" = Table.NestedJoin(#"Nome do Dia Inserido1", {"Data"}, Tabela1, {"Data"}, "Tabela1", JoinKind.LeftOuter),

#"Tabela1 Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas1", "Tabela1", {"Feriado"}, {"Feriado"}),

#"Coluna Condicional Adicionada1" = Table.AddColumn(#"Tabela1 Expandido", "Dias Úteis", each if [Nome do Dia] = "sábado" then 0 else if [Nome do Dia] = "domingo" then 0 else if [Feriado] = 0 then 0 else 1),

#"Personalização Adicionada" = Table.AddColumn(#"Coluna Condicional Adicionada1", "Hoje", each DateTime.LocalNow()),

#"Tipo Alterado2" = Table.TransformColumnTypes(#"Personalização Adicionada",{{"Hoje", type date}, {"Dias Úteis", Int64.Type}})

#"Tipo Alterado2"

Contagem regressiva de dias úteis no Power BI – Criando Medidas

Pronto, finalizamos o passo de tratamento dos dados. Clique em Aplicar e Fechar.

Agora podemos criar as medidas para realizar a contagem regressiva de dias úteis.

Para o total de dias úteis do mês, clique em Nova Medida:

Dias Úteis do mês = CALCULATE(TOTALMTD(SUM(dCalendario[Dias Úteis]),dCalendario[Data]))

1	Dias Úteis do mês = CALCULATE(TOTALMTD(SUM(dCalendario[Dias Úteis]),dCalendario[Data]))

Para calcular os dias úteis restante até o final do mês, utilizaremos:

Dias Úteis até final do mês = CALCULATE(SUM(dCalendario[Dias Úteis]),DATESBETWEEN(dCalendario[Data],TODAY(),EOMONTH(TODAY(),0)))-1

1	Dias Úteis até final do mês = CALCULATE(SUM(dCalendario[Dias Úteis]),DATESBETWEEN(dCalendario[Data],TODAY(),EOMONTH(TODAY(),0)))-1

* Print tirado no dia 01.06.2020

Observar que no cálculo para Dias Úteis até Final do Mês não considera o dia de hoje como um dia útil, por isso o -1 após fechamento do CALCULATE.

E aí, foi simples fazer um calendário contando os dias úteis e ainda fazer uma contagem regressiva de dias úteis em calendário no Power BI?

Caso não entenda algum ponto dele entre em contato conosco pelos comentários abaixo ou pelo nosso e-mail falecom@uaismart.com.

Compartilhe!

12/06/2020/0 Comentários/por Fabio Kendi Akamine

Aprenda Power Query

Web Scraping no Power BI – Aprenda a melhor forma

Certamente você já utilizou o Web Scraping em algum momento de sua vida, seja na acadêmica ou profissional, você precisou das informações contidas em uma tabela ou algum texto dentro de um site, certo? Mas, já fez Web Scraping no Power BI?

Neste post ensinaremos a trabalhar com Web Scraping no Power BI de uma forma simples e resolvendo alguns possíveis problemas que encontrará no caminho.

Sabia que de uma certa forma, você já estava praticando o Web Scraping? A maneira que encontrávamos de fazer isso era o bom e velho Ctrl C + Ctrl V para adquirir estas informações das tabelas de um site.

Hoje, com a necessidade de agregar mais informações contidas em sites, o Web Scraping – Raspagem de Página da Web (em tradução livre) – se tornou uma atividade bem utilizada para formação ou complementação de seu conjunto de dados para serem catalogados e analisados posteriormente. Um exemplo é a ferramenta SEO (Search Engine Optimization) do Google. Através do Googlebot, não apenas o Google recolhe as informações contidas nos sites, como também o SEO os ranqueia de acordo com sua relevância, retornando os resultados de busca de forma mais eficiente e assertiva para o usuário.

Uma maneira fácil de realizar o Web Scraping no Power BI é estabelecer uma conexão direta com a site e realizar o tratamento dos dados através do Power Query, mas caso a plataforma que você for utilizar possuir um API (Application Programming Interface), você pode consultar o artigo que temos aqui no site, onde falamos sobre conectores de API no Power BI.

Web Scraping com Power BI – Extração dos dados

Primeiramente, você precisa acessar a URL de interesse. Para exemplificar o conteúdo deste post, utilizaremos a url das informações de IPCA do IBGE. Utilizaremos os indicadores do IPCA referentes ao período de Março-2020. Nesta URL, é interessante notar que a extensão final é modificada de acordo com o período consultado.

Após temos o endereço da URL das informações que buscamos, partimos para Web Scraping no Power BI. No programa, iniciamos a conexão a uma fonte de dados web (Obter dados -> Web).

Para extrair as informações da web, podemos utilizar 2 modos. O 1º Modo utiliza tabelas já estruturadas do próprio site ou que as tabelas sugeridas pelos algoritmos do Power BI , enquanto que o 2º Modo nenhuma tabela estruturada nativa do site ou as tabelas sugeridas pelos algoritmos do Power BI te atendendem, deste modo, utilizaremos a opção Adicionar a Tabela Usando Exemplos.

No 2º modo será habilitado uma tabela em branco e o site para você inserir as informações nas colunas. Posteriomente, selecione as informações através das caixas de opções disponibilizadas quando você começa a buscar os dados de interesse.

De uma forma bem simples, estes elementos disponibilizados no momento de preenchimento da célula são utilizados para “calibrar” o padrão das informações que serão retiradas do site. É importante observar que caso haja mais informações a serem captadas que o autopreenchimento (letras em cinza claro) sugeriu, será necessário que você preencha mais células em branco para que o Power BI entenda que precisa recolher mais informações. Depois, aperte OK.

*Obs: No exemplo de Web Scraping no Power BI abaixo, não precisamos nos preocupar em inserir a informação de data em uma coluna, pois as informações necessárias já estão contida na URL da página (explicarei logo em seguida).

Consulta em Múltiplas Páginas no Power BI

Após transformar as colunas com os Tipos de Dados corretos, podemos estender a consulta para outros períodos de interesse. Se lembra que anteriormente, no começo do artigo, indico a variação da URL de acordo com os períodos consultados (Mês-Ano), certo? Pois bem, está aí o padrão desta URL.

Através do Power BI podemos utilizar uma variável para requisitar informações de outros períodos inserindo uma variável na URL (no lugar do padrão observado) e transformar a Tabela 9 em uma função de consulta. Essa é uma das grandes vantagens do Web Scraping no Power BI.

Inicialmente ,no Editor avançado, substituiremos a extensão padronizada no final da URL marco-2020 por uma variável (“&período&”). Em seguida, inserimos uma linha de código antes da expressão let, que nos retornará o resultado da consulta em forma de tabela “(período as any) as table => ”, conforme imagem abaixo. As modificações realizadas irão transformar a tabela em uma função de consulta.

Em seguida, construiremos uma tabela auxiliar com os períodos a serem consultados (mês-ano) no Web Scraping.

Pode ser feito de 2 modos:

1º Modo) Importando uma tabela do Excel (.csv ou .xlsx) ou em Texto (.txt) com os períodos que serão consultados.

2º Modo) Cria-se uma Tabela auxiliar dentro do Power BI através do Power Query: “Página Inicial” (1) -> “Inserir Dados” (2) -> OK.

Posterior à etapa da criação da tabela de data (veja figura abaixo), clique na aba “Adicionar Coluna” (1), selecione a opção “Invocar Função Personalizada” (2) e aplique a função criada na coluna com os períodos. Assim que aplicada, a função retornará linhas que conterão tabelas com os novos períodos, baseado na tabela tratada criada.

Na figura abaixo, após a coluna ser expandida (1), pode-se deselecionar o uso do nome da coluna original como prefixo (2), assim, suas colunas manterão os nomes padronizada.

Após expandir a coluna, temos que realizar apenas um pequeno ajuste para que você consiga transformar a coluna de períodos em uma coluna no TIPO DATA. Para isso, é necessário a corrção ortográfica da palavra MARCO para MARÇO, só assim o Power BI reconhecerá que se trata de uma data. Finalmente, selecione “Transformar” (1) e depois “Substitui Valores” (2), fazendo a correção ortográfica.

Pronto, cumprimos as principais etapas de Web Scraping no Power BI. Agora é só realizar o tratamento de dados e aplicar as mudanças realizadas.

Possíveis erros durante o Web Scraping

Durante o processo de Web Scraping no Power BI, é provável que você encontre este erro ao tentar “Fechar e Aplicar”.

Failed to save modifications to the server. Error returned: 'Expression in partition 'n/a' in table 'n/a' references an unknown entity.

1	Failed to save modifications to the server. Error returned: 'Expression in partition 'n/a' in table 'n/a' references an unknown entity.

Deste modo, será necessário desabilitar a função de “Armazenar os conjuntos de dados usando o formato de metadados aprimorados” (1), pois esta função dos Recursos de Visualização do Power BI não é compatível com os seguintes recursos e conectores:

1. Scripts do Python	2. Conectores personalizados
3. Azure DevOps Server	4. Conector do BI
5. Denodo	6. Dremio
7. Exasol	8. Indexima
9. IRIS	10. Jethro ODBC
11. Kyligence Enterprise	12. Mark Logic ODBC
13. Qubole Presto	14. Team Desk
15. Expressões M que contêm determinadas combinações de caracteres, como “\n” em nomes de colunas	16. Ao usar conjuntos de dados com o recurso metadados aprimorados de conjunto de dados habilitado, as fontes de dados de SSO (logon único) não podem ser configuradas no serviço do Power BI

Arquivo -> Opções e Configurações -> Opções -> Recursos de Visualização

Viu como é melhor fazer o Web Scraping pelo Power BI? Como resultado, ele ajuda muito na economia de tempo na extração de dados da web de forma constante e padronizada.

Este post tem um nível avançado e caso não entenda algum ponto dele entre em contato conosco pelos comentários abaixo ou pelo nosso e-mail falecom@uaismart.com.

Compartilhe o artigo pelas redes sociais para mostrar aos seus amigos como se trabalha de verdade com Web Scraping.

11/05/2020/0 Comentários/por Fabio Kendi Akamine

Aprenda Power Query

Como criar coluna condicional no Power BI com Power Query

Você vai descobrir que é possível fazer praticamente tudo utilizando esta forma de adicionar colunas. Leia este artigo e aprenda a criar coluna condicional no Power BI através do Editor de Consultas. Este artigo também serve para quem quer fazer o mesmo no excel através do Power Query.

Se quer saber mais sobre o Power Query e a Linguagem M (a linguagem do Power Query) acesse este artigo: A LINGUAGEM DA POWER QUERY NO POWER BI.

Em muitos casos será necessário colocar expressões condicionais no seu modelo. A função IF, que faz isso na linguagem DAX, é uma função que considera linha a linha. Dependendo do tamanho do seu modelo, isso pode ser um problema. Pode causar muita utilização de memória e com isso deixar o processamento do relatório lento. Nestes casos é melhor fazer uma coluna condicional no Power BI usando o Power Query.

A opção de adicionar coluna condicional à primeira vista pode parecer um pouco limitada, mas mostraremos abaixo que é perfeitamente utilizável em cenários complexos também. Será útil em vários casos.

Para seguir todas as etapas e criar coluna condicional no Power BI com todas as possibilidades, faça o download da base que utilizamos como exemplo. Com isso você pode seguir as demonstrações abaixo da mesma forma como apresentamos a seguir.

Adicionando coluna condicional com Power Query

Na imagem abaixo temos a coluna com o número de filhos (Nro_Filhos). O que queremos neste primeiro exemplo é classificar as famílias, ou seja, dizer o tamanho da família conforme a quantidade de filhos. Vamos classificar como “Família Pequena” de 0 a 1 filho, como “Família Média” de 2 a 3 filhos e “Família Grande” acima de 3 filhos.

Importe esta tabela para o Power BI ou Power Query do Excel através do Obter Dados. Seguiremos a partir de agora pelo Power BI, mas o processo no Power Query do excel é o mesmo.

Após os dados extraídos da base que disponibilizamos entre no Editor de Consultas. Então vá até a faixa de opções Adicionar Coluna e depois Coluna Condicional.

Após clicar em coluna condicional aparecerá a tela abaixo.

Nesta tela, inicialmente, terá apenas uma linha para a regra, então clique em Adicionar Regra. No campo “Nome da nova coluna” coloque o nome para esta coluna. Nas linhas de regras escolha a coluna em “Nome da Coluna”, no caso será a coluna “Nro_Filhos”. Defina o restante das regras, Operador, Valor, Saída. Por fim o “Caso Contrário”, tudo conforme feito na imagem acima. Terminando tudo, clique em OK.

Vemos na criação da coluna condicional (figura acima) a seguinte lógica. Se “Nro_Filhos” for menor ou igual a 1 então “Pequena”. Senão se “Nro_Filhos” for menor ou igual a 1 então “Média”, caso contrário (se não atender nenhuma das regras) “Grande”.

Na hora de definir as regras e inserir os parâmetros enquanto cria a coluna condicional no Power BI, é possível fazer algumas escolhas. Além de Adicionar Regra conforme falado acima pode-se também escolher entre valores estáticos (Digite o valor), valores de uma coluna (Selecionar uma coluna) ou parâmetros previamente definidos. Veja isso na figura abaixo.

É possível também excluir ou mudar a ordem das condições para cima ou para baixo. Veja onde na figura abaixo.

Mas como adicionar os operadores AND e OR?

Vimos até agora que criar uma coluna condicional com Power Query é bem simples. Mas você deve estar se perguntando como incluir as lógicas do AND ou OR nas condicionais? Muitas vezes a condições devem levar em conta mais de uma coluna ou mais e de uma situação. Nesses casos é necessário utilizar os operadores lógicos AND e OR.

No processo de criar uma coluna condicional no Power BI (excel também), temos três formas para considerar estes operadores.

Para demonstrar estas três formas vamos criar uma condicional que retorne um ”Título” baseado nas colunas “Estado Civil” e “Gênero”. Já existe esta coluna “Título”, mas está errada e faltando informações. No quadro abaixo está a lógica que iremos implementar.

Na base de dados não há o gênero “Outro”, mas vamos utilizá-lo na lógica. Com isso, se você precisar de algo neste sentido já está contemplado na explicação.

1ª Forma: Ordem das Condições

A ordem das condições tem impacto no resultado. Tenha isso em mente. É por isso que há a possibilidade de mover as condições para cima ou para baixo na lista.

Observe que numa operação condicional há três partes:

Se (Lógica) é verdadeiro, então (faça isso) caso contrário (faça aquilo)

Então pense o seguinte: a execução somente vai para a próxima condição, SOMENTE SE não atender a condição anterior. Seguindo esta forma de pensar, abaixo está a solução para a primeira forma.

A primeira e segunda condições são bem simples, apenas precisam levar em conta a coluna “Gênero”, pois não há necessidade de checar nada na coluna “Estado Civil” para chegar na conclusão. O grande truque está na terceira condição. Nesta há necessidade de checar o “Estado Civil”. Mas somente chegará nesta terceira regra se as duas primeiras não forem verdadeiras. Isso implica que somente chegará lá se o Gênero não for masculino ou outro.

A terceira condição será acionada somente se for feminino. Nesse momento é que precisará checar a coluna “Estado Civil”, se solteiro será Srta., caso contrário Sra.

Sabemos então como funciona o Se, o Senão se e o Caso contrário. Entendemos também que colocar as condições na ordem correta interferirá no resultado da condicional. Praticamente dá para usar esta forma em todos os casos. Basta mudar a forma de pensar, colocar as condições numa ordem lógica considerando o alcance de cada regra.

2ª Forma: Concatenando as colunas

Esta é uma forma mais simples para criar uma coluna condicional no Power BI considerando mais argumentos. Provavelmente é a mais usada neste tipo de caso. Nesta situação criamos uma coluna mesclando as colunas “Gênero” e “Estado Civil”. Abaixo o procedimento para adicionar coluna mesclando as duas colunas.

Assim utiliza esta nova coluna para definir as condições conforme abaixo.

Nesta forma colocamos um passo a mais nas etapas do Power Query, que é a criação de uma nova coluna mesclada. Preferimos mais a primeira forma por utilizar mais a visão lógica e não ter que colocar uma etapa a mais. Mas por ser mais fácil pode ser a preferência da maioria.

3ª Forma: Utilizando a Linguagem M

Se você já tem mais familiaridade com o Power Query talvez já entenda um pouco mais das funções e fórmulas utilizando a Linguagem M. Essa é a linguagem do Power Query. As possibilidades utilizando esta forma de fazer serão bem maiores.

Para utilizar os operadores AND e OR com M basta clicar em Coluna Personalizada, na faixa de opções Adicionar Coluna. Feito isso escreva as linhas abaixo e teremos os mesmos resultados que as 2 formas anteriores, mas agora numa pegada de desenvolvedor e utilizando códigos.

Veja que utilizamos AND no exemplo, mas é possível utilizar o OR também. A Linguagem M é sensível a letras maiúsculas e minúsculas. Assim o AND ou OR devem ser escritos em minúsculos.

Pronto para criar coluna condicional no Power BI?

Criar coluna condicional no Power BI é uma ótima maneira implementar uma lógica linha a linha, mas sem o peso todo que uma coluna calculada ou uma medida que utiliza IF traz para o modelo.

Pela interface gráfica do Power Query dá para implementar praticamente todas as lógicas possíveis. Em algumas lógicas você terá que alterar a ordem de pensamento. Mas é só tentar pensar de uma forma diferente organizando a ordem das regras para chegar no resultado. Você pode também criar as lógicas utilizando a Linguagem M. Fica conforme for mais simples ou melhor para você.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto. Ajude-nos também a espalhar bons conteúdos sobre o Power BI.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

Referência: RADACAD

25/07/2019/0 Comentários/por Marcelo de Tarcio

Aprenda Power Query

Como conectar a bases via Conector de API no Power BI

A utilização API vem crescendo muito, a medida que os desenvolvedores, permitem cada vez mais, interações diretas em seus programas e aplicativos. Ao longo deste artigo vocês aprenderão a trabalhar com os conectores de API no Power BI.

Como vocês bem sabem o Power BI é um programa voltado para a extração, tratamento e apresentação de dados. Conectar a uma fonte de dados via API no Power BI pode ser uma ótima alternativa para poupar trabalho e garantir dados diretamente da origem.

Por exemplo, quer extrair todo seu planejamento de atividades diretamente a um aplicativo que usa frequentemente? Conecte-se a sua API no Power BI, trabalhe no processo de ETL e pronto. Conseguirá extrair as informações, trata-las e apresenta-las de forma visual, fluída, com diversas opções de visualizações, inclusive no celular.

Mas antes de entrar no exemplo prático que separamos para este artigo sobre como trabalhar com API no Power BI, vamos entender o que é API.

O que é API

API é um conjunto de rotinas e padrões de programação para acesso a um aplicativo de software ou plataforma baseado na Web. A sigla API refere-se ao termo em inglês “Application Programming Interface” que significa, em tradução livre para o português, “Interface de Programação de Aplicativos”.

Uma API é desenvolvida quando um criador tem a intenção de que outros criadores de software desenvolvam produtos associados ao seu serviço. Existem vários deles que disponibilizam seus códigos e instruções para serem usados em outros sites. O Google Maps é um dos grandes exemplos na aplicação de APIs. Por meio de seu código original, muitos outros sites e aplicações utilizam os dados do Google Maps adaptando-o da melhor forma no uso desse serviço.

É o que acontece com os softwares e aplicações que disponibilizam uma porta, por meio de APIs, para acessarmos os dados que eles produzem. Assim é possível acessar dados e fazer interações com o Facebook, Instagram, Linkedin, Google Analytics, Youtube, Trello, Asana, etc. Além desses inúmeros outros programas conectando-se às API de gráfico e dados deles.

Conectar API no Power BI é possível com todas as plataformas citadas acima. Com o conteúdo neste artigo você conseguirá utilizar os conectores predefinidos do Power BI sem grandes dificuldades. Agora que entendeu o que é API vamos para nosso exemplo.

Conectando uma API no Power BI

Para melhor explicar o funcionamento de uma API no Power BI vamos utilizar a analogia de uma academia. No caso, uma academia chamada MAILCHIMP. O Mailchimp, para quem não conhece, é o site de e-mail marketing muito utilizado por ser gratuito até certa quantidade de inscritos. A recepção da academia é o API (a porta de entrada a todas as possibilidades que o MAILCHIMP pode oferecer).

Por esta porta de entrada (API) eles podem dar acesso a ler informações, inserir, modificar e deletar. Através da recepção da academia você pode ter acesso a várias partes dela. Podemos citar: vestiários, lanchonete, salas de pilates, salas de lutas e mais. Estes locais dentro da estrutura (na conexão via API) são chamados de “endpoints”. Mas para passar para cada um destes “endpoints” é necessário passar também pela catraca, neste caso no API estamos falando do “Token de Autorização”, que garante entrar dentro da API e explorar tudo que o mesmo lhe der permissão de explorar.

Agora falando de forma mais técnica a recepção da academia pode ser acessada no seguinte endereço: https://us19.api.mailchimp.com/3.0, mas o endereço exato da sala de pilates seria: https://us19.api.mailchimp.com/3.0/sala-pilates. Para ir diretamente à sala de pilates utilizando esta API no Power BI ou o Power Query do Excel, podemos criar uma consulta, utilizando a URL acima. Nesta URL, provavelmente será necessário adicionar um token de autorização. No caso de sala-pilates é um endpoind apenas para ilustrar o exemplo, não existe, mas podemos dar outro exemplo mais real: https://us19.api.mailchimp.com/3.0/lists.

Mas como ter autorização?

Você deve estar se perguntando agora, como eu consigo este Token de Autorização? Isto vai depender, na verdade, do serviço de API disponibilizado. Simplesmente você pode usar algo chamado “API Key” ou em outros casos o método “OAuth”. Para este exemplo utilizaremos OAuth, que consiste em ser autenticado, através de um formulário, durante o processo de conexão. A conexão será feita pelo conector direto com o mailchimp que já vem na lista de conectores pré-instalados do Power BI.

O processo normal para acessar dados a partir de uma API no Power BI, consiste em:

Chegar na recepção e pedir sua autorização de entrada. API com token de autorização de acesso.
Ir até o “endpoint” desejado (Sala de pilates, de lutas, etc.);
Solicitar o que deseja (ler dados, inserir dados). Se está na lanchonete seria o equivalente a pedir um shake. Para ler dados você lança uma solicitação GET, para inserir uma solicitação POST.

Conseguimos conectar pela API do mailchimp no Power BI apenas via conector próprio dentro de Obter Dados do Power Query (Editor de Consultas). De certa forma isto é uma limitação, pois poderia ser disponibilizado uma conexão pelo endereço da Web colocando um token na URL e todo o caminho dos endpoints. Em algumas APIs isto é possível de ser fazer, por exemplo as do Facebook e Instagram.

Usando um conector de API no Power BI Desktop

Faremos neste artigo a demonstração de como se conectar a API no Power BI por conectores disponíveis em Obter Dados, em artigos futuros demonstraremos também como se conectar diretamente, sem utilizar os conectores.

Utilizaremos então, como exemplo, a conexão com o MAILCHIMP. Caso queira acompanhar e fazer os mesmos passos abaixo você precisará:

Conta no MailChimp, de preferência que tenha lista de e-mails e campanhas de e-mail marketing;
Última versão do Power BI Desktop.

Vá até Obter Dados, na caixa de diálogo que se abre, clique em Serviços Online, em seguida Mailchimp (Beta) e finalmente Conectar. Vai aparecer nesse momento uma janela onde você entrará com o login e senha da sua conta do Mailchimp. Esta é a autenticação OAuth 2.0 acontecendo bem neste momento.

Na caixa com a lista de conectores dos serviços online, você verá todas as opções disponíveis de conexões diretas e nativas do Power BI. Há diversos tutoriais para cada um deles na internet, mas caso não encontre nos fale que tentamos te ajudar.

Na próxima janela, Navegador, é onde terá todas as opções disponíveis deste conector.

Veja que aparece 2 pastas e 2 funções na janela. Estas opções podem mudar no caso de conectores para outras aplicações.

Pastas

Campaigns: Esta pasta contém consultas pré-definidas que lhe trará dados relativos às suas campanhas de e-mail marketing. São funções GET prontas para serem trabalhas apenas escolhendo qual irá utilizar nesta pasta.
Lists: Esta pasta contém consultas pré-definidas que lhe trará dados relativos às suas listas de e-mail. Também são funções GET prontas para serem trabalhas.

Funções

Mailchimp.collection: Esta é uma função simples (não é um conjunto de funções ou pacote de consultas), que tem um fluxo que cria sua própria função GET ou POST ou qualquer outra solicitação que deseja que seja feita. A única entrada necessária é o nome da coleção de dados, por exemplo. Lists ou Reports.
Mailchimp.Instance: É parecida com a função anterior, mas com esta pode apontar diretamente a um elemento específico da API no Power BI, como o “endpoint”: email activity.

Utilizando as opções das Pastas

Continuando nosso exercício, selecione a opção Campaign Summary da pasta Campaigns.

Clique em seguida em Editar.

Esta consulta trará todas as campanhas feitas na sua conta em todas as listas de e-mails criadas até então. Se quiser focar nas campanhas de uma lista específica é só filtrar na tabela pelo ID da lista. No processo de tratamento dentro do Editor de Consultas é possível fazer isso. Além disso, você consegue também fazer uma série de tratativas e transformar esta consulta em uma rica fonte de informações sobre suas campanhas.

Começa com as informações gerais de cada uma das campanhas e daí dá para expandir mais linhas por uma série de outras colunas. Podemos expandir aberturas de e-mails, cliques, encaminhamentos, status e mais. Você pode fazer do jeito que melhor lhe convier. Perceba que há várias possibilidades nesse processo de extração das informações via API no Power BI.

Como estamos tratando um exemplo simples, veja abaixo que filtramos apenas 3 colunas, data do envio da campanha e id e id da lista.

Esta tabela pode ser usada como uma tabela de dimensões num modelo de dados e com isto se relacionar com outras tabelas para outras buscas nesta API do Mailchimp.

Utilizando uma função na API do Mailchimp

Vamos agora complementar a tabela utilizando a função Mailchimp.Instance. É uma forma de fazer nossa própria função GET nas atividades de e-mails (email activities).

Vá em Adicionar Coluna e em seguida Coluna Personalizada. Dê o nome para a coluna e digite a fórmula que está na figura abaixo.

A partir deste ponto você poderá ir expandindo as colunas, registros e listas até chegar no resultado desejado.

O intuito do artigo é lhe dar uma introdução no trabalho com os conectores de API no Power BI. Não era propósito desenvolver um relatório completo. Através da sua curiosidade, pesquisa e necessidade você conseguirá desenvolver o relatório conforme precisar.

Através dos conectores de API no Power BI é possível se plugar a uma série de informações de vários sites e aplicativos, mas como dito mais acima é possível também fazer isso por APIs externas também.

Cada aplicativo, software e site tem sua forma de fazer isso e em todos você encontrará tutorias de como fazer. Procure na documentação destas aplicações para maiores detalhes. Explore este mundo de possibilidades.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto. Ajude-nos também a espalhar bons conteúdos sobre o Power BI.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

13/03/2019/0 Comentários/por Marcelo de Tarcio

Aprenda Power Query

Melhores práticas na importação de dados no Power BI

Neste artigo você encontrará, as melhores práticas que nós da uaiSmart consideramos para as atividades de importação de dados no Power BI. Lembrando que essas recomendações servem também para o Power Pivot do Excel.

Nos seus modelos de dados no Power BI, considere sempre utilizar os nomes de forma intuitiva, padrão e quais colunas incluir. Isto lhe trará melhoras na interface e na performance deles.

Nem todas as práticas descritas poderão ser aplicadas a todos os modelos de dados. Será necessário então adaptar nossas sugestões conforme sua realidade. O mais importante é que você consiga passar sua mensagem em seus arquivos, então considere as vantagens e desvantagens de cada escolha.

Utilize nomes que façam sentido na importação de dados no Power BI

Tanto os nomes para as tabelas quanto para as colunas devem ser amigáveis, intuitivos e ter significado para quem utiliza o modelo.

Deve-se remover qualquer prefixo ou sufixo que existam nas tabelas ou colunas que dão origem aos dados importados. É comum ver prefixos como “tb”, “tbl”, “vw”. Não há necessidade destes na hora da importação de dados no Power BI. Assim é melhor ter uma tabela de nome “Clientes” do que “tb_Clientes”. Este nome pode ser necessário no banco de dados SQL, Oracle ou qualquer outro, para identificação e padronização, mas para o Power BI não. Quanto mais intuitivo e simples o nome melhor para identificar e trabalhar com ele.

Evite também abreviações, prefixos e sufixos nos nomes das colunas. Mas para casos de abreviações bastante conhecidas tudo bem, pois estas não causarão confusões. Exemplo, utilize “Total de Vendas” ao invés de “Ttl Vendas”, ou “# Vendas” ou “TotalVendas”. Você pode usar espaço, caracteres especiais ou letras com acentuação sem nenhum problema dentro da importação de dados no Power BI. A ideia é simplificar a vida de quem utiliza os modelos.

Evite nomes repetidos ou parecidos nas colunas e medidas

Sempre carregue as colunas numéricas no modelo de dados utilizando nomes que não se confundam com as medidas. Caso não vá utilizar a coluna pura, oculte para que não apareça na Visualização de Relatório. Assim poderá criar à medida que utiliza aquela coluna com o nome que melhor descrever e não irá ficar repetindo, pois uma se manterá oculta.

Pense no nome da medida já de forma antecipada. Se quer apresentar o nome “Total de Vendas” como a soma de todas as vendas, então não use o nome como um nome de coluna. Se usar o nome de uma coluna como medida após a importação de dados no Power BI, este vai negar, pois não se pode ter o mesmo nome de coluna em medida.

Usar nomes tais como “Soma do Total de Vendas” para medidas não é uma boa solução. Nomes assim não soam bem. Por exemplo, você pode importar o “Total de Vendas” como “Valor das Vendas” ou “ValorVendas” (sem espaços mesmo, já que vai ficar escondida). Assim crie a medida “Total de Vendas” a partir desta coluna e esta ficará à disposição do usuário.

Total de Vendas = SUM ( Vendas[ValorVendas] )

1	Total de Vendas = SUM ( Vendas[ValorVendas] )

Remova colunas desnecessárias na importação de dados no Power BI

Não mostre na visualização de relatórios uma coluna que não seja necessária para o modelo de dados do Power BI.

Mesmo que não saiba antecipadamente qual coluna será ou não útil para seu modelo. Durante a confecção do relatório você acabará descobrindo todas, mas mesmo assim tente mostrar somente aquelas necessárias. Após apurar que uma coluna não precisa ser mostrada, volte e oculte a mesma.

Com a redução de colunas expostas na visualização do relatório, reduz também a quantidade de memória utilizada pelo Power BI. Esta prática evita também expor colunas de informações sensíveis que são utilizadas somente por razões técnicas, tais como colunas de chaves numéricas, nomes de usuários, quem fez a última modificação, etc.

Menor número de colunas no processo de importação de dados no Power BI significa menor número de dependências entre tabelas físicas e relatórios. Evita assim também grandes necessidades de manutenções futuras em caso de mudança de base, por exemplo.

As colunas em cinza na imagem acima estão ocultas na Visualização do Relatório.

Divida as colunas de data e hora

No caso de uma coluna que tenha tanto a data quanto a hora juntas é melhor dividir em duas. Uma para Data e outra para Hora.

Uma coluna contendo data e hora contém dados mais complexos que exige mais do Power BI. Dividindo em duas colunas conseguirá economizar memória, vai melhorar a performance e deixar o modelo de dados mais fácil de usar.

Sabe trabalhar com mapas no Power BI? Não? Temos um e-book que desvenda todos os segredos dos mapas no Power BI. Clique na figura abaixo e baixe gratuitamente.

Aplique a opção “Marcar como tabela de data” em tabelas com datas

As funções de inteligência de tempo têm melhor desempenho quando a tabela “Calendário” tem esta categorização. Em alguns casos de relacionamento isto se faz até mandatório para que o mesmo funcione. É o caso de coluna chave substituta.

Mas mesmo nos casos não obrigatórios é uma boa prática fazer a marcação. Assim a interface de usuário e outras funcionalidades do Power BI serão melhoradas e dará ao usuário uma melhor experiência na utilização do modelo.

Conclusão

Boas práticas de importação de dados no Power BI estão sempre surgindo, neste artigo disponibilizamos algumas. Caso você conheça outras deixe seu comentário abaixo que pesquisaremos e agregaremos no conteúdo. Caso também descubramos mais atualizaremos aqui.

Estas práticas se aplicam também se você é administrador de banco de dados SQL e irá disponibilizar a usuários de Power BI uma “View” do banco. Aplique estas práticas e o melhor é por “View” mesmo.

Este processo faz parte da parte de ETL, quer saber mais sobre este assunto leia o artigo: O que é ETL e sua importância para o Business Intelligence.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto. Ajude-nos também a espalhar bons conteúdos sobre o Power BI.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

10/07/2018/0 Comentários/por Marcelo de Tarcio

Aprenda Power Query

O que é ETL e sua importância para o Business Intelligence

Abaixo você entenderá, da maneira mais completa possível, o que é ETL. A sigla em inglês, Extract, Transform and Load (Extrair, Transformar e Carregar). Buscamos de forma simples explicar para que o leitor leigo no assunto possa compreender o assunto e o leitor intermediário consolidar seu conhecimento.

Quando falamos sobre o que é ETL, falamos de um processo de extração e adaptação de dados de fontes externas. O conceito de ETL se confunde muito com ferramentas que fazem o processo, pois um não se realiza sem o outro.

O entendimento geral é de que esta é a fase mais crítica da construção de um modelo de dados. Caso você vá montar um Data Warehouse é o processo mais importante e trabalhoso a ser feito, pois, toda a estrutura de dados depende dele. Não há Business Intelligence sem ETL.

A consolidação dos dados é feita a partir de diferentes fontes. Estas fontes podem ser banco de dados relacionais, arquivos de texto, arquivos em excel, endereços na Web, dentre inúmeros outros tipos de fontes. O sistema de ETL tem que conseguir se comunicar com as diversas fontes de dados, ler diferentes formatos e como resultado trazer tudo numa base homogênea. O produto final do processo de ETL será uma base para gerar relatórios e análises que suportem decisões de negócios, não podem ter erros nestes resultados.

No mercado há diversas ferramentas para estas atividades, algumas bastante conhecidas e consolidadas. Citamos aqui SAS, Microsoft SQL Server Integration Services, Power Query (excel e Power BI), Pentaho Data Integration, Oracle Data Integrator, SAP BusinessObjects Data Services, dentre outros.

Extração, Transformação e Carregamento (o que é ETL)

O processo de ETL envolve os seguintes passos, como o próprio nome indica:

Extração dos dados de fontes externas;
Transformação dos dados para a realidade de cada negócio;
Carregamento dos dados nos modelos de dados deixando-os disponíveis para trabalho.

Como o processo de ETL funciona.

Extração

A primeira etapa no processo é extrair os dados nas origens das fontes. Os projetos em geral consolidam diferentes fontes com diferentes formatos dos dados, organizados também em estruturas diferentes.

Veja abaixo exemplos das fontes de dados mais utilizadas:

Arquivos de Texto/CSV;
Excel;
Banco de Dados Relacionais;
Banco de Dados Cloud;
JSON;
Endereço WEB;
Serviços Online por API;
Etc…

Transformação

Consiste no tratamento, limpeza e adaptação dos dados extraídos. Desta transformação são gerados os dados a serem carregados.

A parte de transformação consiste em padronizar os dados em relação ao tamanho e tipo. Remover colunas ou linhas indesejadas. Substituir caracteres estranhos, corrigir erros de digitação e padronizar nomes e termos. Criação de novas colunas a partir de colunas já existentes contendo somente parte dos dados. Criação de colunas a partir da junção de duas colunas. Transposição de linhas para colunas ou vice-versa. Traduzir valores codificados em informações legíveis (exemplo: 1 para Masculino, 2 para Feminino). Alteração das unidades de medida. Mesclar ou Acrescentar informações de tabelas em uma só. E muito mais.

Carregamento

Esta etapa, dentro do que explicamos sobre o que é ETL, representa em colocar os dados transformados dentro do modelo de dados ou Data Warehouse. Este processo varia de acordo com a aplicação, necessidade e organização.

Com a carga dos dados é possível ler e gerar valor sobre eles. A partir deste ponto o analista ou gestor de negócios consegue manipular e formar seus relatórios e análises para o processo de tomada de decisão.

A frequência de carregamento e a quantidade de dados a serem armazenados são definidas nesta etapa também. Cada empresa e pessoa tem necessidades específicas. Com relação à frequência é importante programar uma periodicidade em que os dados serão atualizados e utilizados e num horário que não tenha grande utilização de recursos de rede, pois é um processo que exige do ambiente ou da máquina que irá executar.

Considerações importantes para o entendimento sobre o que é ETL e para trabalhar melhor nos processos

A chave de um bom processo de ETL e prestar atenção em alguns fatores.

Disponibilidade das fontes de Dados

Suas fontes devem ser de fácil acesso e pouco sujeitas a alterações. Busque se basear em fontes onde você ou sua empresa tenham uma certa influência. Devem ser evitados constantes alterações na estrutura, nos locais de armazenamento ou caminho de busca bem como alterações no próprio conteúdo, isto causa inconsistências no modelo de dados e necessidade de ajustes constante nas etapas de extração e transformação.

Simplifique sua vida

É nesta parte que você pode otimizar seu trabalho e deixar sua vida mais fácil na geração de seus relatórios e modelos de dados. Preste bastante atenção na hora de transformar, faça isto para reduzir a necessidade de intervenção no dia a dia, para deixar a atividade mais automatizada possível.

Escalabilidade de dados

A tendência é que os dados somente cresçam e se acumulem, fazendo com que consuma cada vez mais recursos do sistema. Isto deve ser pensado no momento da criação do modelo, deve-se entender o tamanho que vai chegar. Dependendo deste tamanho a estratégia de atualização muda, será necessário criar rotinas quebradas com vários processamentos. Então para não ter surpresas e travamentos já pense nisto antes.

As empresas de desenvolvimento de softwares de ETL desenvolveram formas de minimizar os impactos de grandes processamentos. É chamado de processamentos paralelos, dando mais capacidade às suas soluções.

Recomendamos a leitura do artigo a seguir com as nossas melhores práticas para importação de dados no Power BI.

Conclusão

Nossa intenção neste artigo, além de fazer você entender o que é ETL, foi também dar mais contexto a este processo. Espero que tenham compreendido a importância desta etapa no todo do Business Intelligence.

Por fim, entenda o que é ETL como sendo a ponte que liga os dados até o modelo de dados. Sem esta ponte os dados não chegam ao seu objetivo, que é informar, dar subsídios, possibilitar tomada de decisão consciente.

O processo deve ser bem planejado e executado de forma a evitar erros e retrabalhos futuros, mas principalmente de forma a reduzir o tempo de execução no tratamento dos dados. Esperamos que tenham gostado e entendido o que expusemos em relação ao que é ETL.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

22/02/2018/0 Comentários/por Marcelo de Tarcio

Aprenda Power Query

Como listar as funções do Power Query no Power BI. Biblioteca das funções: #shared

O Power Query é uma linguagem funcional. É baseada em linhas digitadas composta por funções diversas. São inúmeras as funções que estão disponíveis para serem usadas, mas não adianta nada ter tantas se não soubermos quais são. O código #shared serve para listar as funções do Power Query, na verdade todas elas.

Neste artigo será possível entender o quanto esta funcionalidade #shared é útil. Provavelmente na primeira vez que listar as funções do Power Query utilizando esta funcionalidade ficará de queixo caído.

Esta é uma dúvida de muitos que começam a trabalhar com Power Query e a sua Linguagem M. Para quem não sabe ainda o que é Linguagem M ou queira entender melhor leia este post (você não vai se arrepender): Linguagem M: A linguagem da Power Query no Power BI.

Com o método descrito abaixo conseguirão encontrar qualquer função de forma fácil, sem a necessidade de conexão à internet para isso. Assim não precisam de mais nada para listar as funções do Power Query, apenas aprender como usar o #shared.

Passo a passo para listar as funções do Power Query

A funcionalidade #shared carrega todas as funções e enumeradores existentes no Power Query em seus resultados. Para tal siga nosso passo a passo para criar uma tabela dentro do Power BI com a lista.

Abra o Power BI Desktop, vamos criar uma Consulta Nula. Assim vá em Obter Dados e escolha esta opção conforme figura abaixo. Clique em Conectar para finalizar a primeira etapa.

Após criar a Consulta Nula será aberto o Editor de Consultas e lá acesse o Editor Avançado.

Aqui é o editor de códigos da linguagem da Power Query e onde se modifica qualquer código criado ou cria novos. Neste momento apague tudo que estiver escrito na tela e digite simplesmente #shared.

Clique em Concluído.

Feito esta ação será carregada a lista de todas as funções e enumeradores do Power Query. Caso tenha outras consultas já criadas neste modelo de dados, aparecerá também na lista gerada. Veja que Consulta1 (que é a consulta que geramos neste exemplo com o código #shared a partir de um modelo de dados em branco) está na lista gerada.

O formato que esta consulta é gerada está em lista de registros, então é necessário expandir estes registros, transformando esta lista em uma tabela para ter acesso a todos os detalhes.

Convertendo lista em tabela

Na janela criada já aparece a opção para converter em Tabela, veja na figura abaixo.

Feito estes passos, você acabou de listar as funções do Power Query. Neste formato de tabela é mais fácil procurar a função desejada.

Por exemplo, vamos procurar as funções de data que tratam mês (month). Após ter transformado a lista em tabela vá na lista dropdown do cabeçalho “Name” e digite “month” no campo Filtro de texto. Veja o procedimento na figura a seguir.

Na tabela será filtrada todas as funções com MONTH no nome.

Documentação das Funções

A partir da consulta criada para listar as funções do Power Query, clicando em cada um dos links da coluna “Value” é possível checar a documentação de cada função. Esta documentação exibe os detalhes da aplicação, formato da linha de código, exemplos de saída dos resultados e mais de cada função.

Clicando no link que mencionamos aparecerá também uma caixa de diálogo para inserir os parâmetros da função. Fazendo isto você consegue simular a aplicação desta função na prática e ainda ver o código gerado.

Nesta caixa de texto que aparece conforme acima digite uma data inicial bem como quantos meses quer que a função considere e verá o resultado da aplicação desta função.

Veja no exemplo abaixo tudo que esta documentação pode fornecer de informações.

Enumeradores

Um dos resultados de listar as funções do Power Query utilizado o #shared são os enumeradores das funções. Estes são parâmetros que algumas funções utilizam para retornar um resultado esperado. Veja abaixo quais são os enumeradores para os dias da semana que devem ser considerados nas funções de dia.

Conclusão

Com isto vocês podem ver o quanto a funcionalidade #shared é útil. Usando ela para listar as funções do Power Query temos acesso a todas as funções e por aqui podemos pesquisar e tirar qualquer dúvida em relação à estrutura, enumeradores e aplicações das mesmas.

Se você for igual a mim que não consegue lembrar de tantas coisas assim, está aí a solução. Nunca mais vai deixar de usar uma função útil do Power Query porque não se lembra. Proceda com os passos acima, gere a lista das funções e aproveite.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

04/01/2018/0 Comentários/por Marcelo de Tarcio

Aprenda Power Query

Linguagem M: A linguagem da Power Query no Power BI

A Linguagem M é uma poderosa linguagem que fica por trás da Power Query e que é responsável por todo o processo de transformação realizado no Power BI e no suplemento Power Query do Excel.

Não é necessário aprender esta linguagem a fundo. Mas entender um pouco do contexto e das estruturas dos códigos gerados lhe ajudará a ganhar mais produtividade nas criações.

Teoricamente a parte gráfica do Editor de Consultas lhe dará boa parte das possibilidades para transformar seus dados. O Editor de Consultas é o Power Query dentro do Power BI. É por ali que a rotina de ETL é realizada. ETL significa: Extract, Transform and Load, ou seja, Extrair, Transformar e Carregar.

É isto que o Editor de Consultas faz e por trás de cada ação realizada, é gerado um código em Linguagem M. Para muitos esta linguagem pode parecer incompreensível ou até mesmo diferente. Neste artigo explicaremos um pouco sobre ela e ao final dele você terá conhecimentos básicos necessários para entender sua lógica e estrutura.

Uma vez que você entenda esta sintaxe/estrutura, tudo se tornará mais simples e será possível efetuar algumas ações no código que aumentará sua produtividade. Os códigos da Linguagem M são acessados pelo Editor Avançado dentro do Editor de Consultas, abaixo você verá mais.

O que é Linguagem M

M é um nome informal para a Linguagem de fórmulas do Power Query (Power Query Formula Language). Como o nome formal é muito longo, resumiram em M. Mas porque M? Vem de Data Mashup, numa tradução livre, Mistura de Dados. Alguns também dizem que é para Data Modeling, Modelagem de Dados.

A linguagem M, na verdade, é mais poderosa e compreensiva do que a interface gráfica do Editor de Consultas. Prova disso é que há sempre atualizações na parte gráfica, ela está em evolução, mas tudo que vem melhorando nesta interface já existe há anos na Linguagem M.

E ainda falta muitas atualizações para a interface gráfica adquirir todas as funcionalidades já existentes no código. Provavelmente não conseguirão colocar todas. Daí uma das razões para se aprender mais sobre M e continuar lendo este artigo até o fim. Sabendo mais você não precisa esperar a próxima atualização da Microsoft para desenvolver seu trabalho.

Estrutura (Sintaxe) da Linguagem M

A sintaxe da linguagem M é bem simples. Sempre é composta de dois blocos de programação: LET e IN. Veja abaixo os blocos num exemplo simples.

let
   y=9
in
   y

let

y=9

LET e IN são palavras reservadas e só podem ser utilizadas no início destes dois blocos. Outra informação extremamente importante na Linguagem M é que ela é sensível à letras maiúscula e minúsculas. Assim é diferente se você colocar y ou Y.

LET: o bloco onde é definido todas as variáveis

IN: os resultados. Tudo que se coloca neste bloco resultam em saídas para sua consulta. Pensando assim este bloco deveria se chamar OUT não IN. Mas é IN, rsrs.

Basicamente, a consulta acima utilizada como exemplo, define uma variável com o nome “y”. É atribuído o valor de 9 a ela, com isto a consulta retornará mostrando 9 como resultado.

Vamos fazer um teste agora e rodar esta consulta e confirmar o que estamos dizendo aqui. De fato todo este artigo é prático, você consegue testar tudo que estou falando abaixo a medida que vai lendo.

Abra o Power BI Desktop, vá em Obter Dados e escolha Consulta Nula. Veja abaixo.

Será aberto o Editor de Consultas. Na faixa de opções Exibição clique em Editor Avançado. Na tela que será aberta digite o código mencionado acima. Feito isso clique em Concluído.

Veja que após o procedimento a consulta retornará o valor 9.

Tanto o let quanto o in devem estar em letras minúsculas, bem como o nome da variável deve ser a mesma nos dois blocos (y).

Quebras das linhas de códigos

As linhas de código no M continuam se você não utilizar o caractere correspondente para fazer a quebra desta linha de código. No exemplo abaixo você vê que mesmo utilizando enter para mudar a linha na tela, esta linha no código não mudou.

Para quebrar a linha de código efetivamente deve-se usa a vírgula (,). Todas as linhas necessitam de uma vírgula para terminar, para a última linha do bloco não é necessário.

Notem que no bloco in, sempre será colocado a última variável, antes era a y agora a z.

Nomes das variáveis

Os nomes das variáveis na Linguagem M podem ser uma única palavra ou mais de uma palavra com espaços entre elas. No caso de ter algum nome que contenha espaço este nome deve ficar entre aspas (“) e também deve contar uma hashtag no início. Veja abaixo como seria.

O nome da variável pode conter também caracteres especiais, conforme exemplo abaixo.

Cada etapa do Power Query

O Power Query é feito por transformações passo a passo. Cada transformação ocorre, geralmente, em um passo. Estas etapas são gravadas e é possível acompanhar e ver cada uma delas no painel que fica no lado direito da tela. É o painel de Etapas Aplicadas.

Veja abaixo o exemplo de cada uma das etapas feitas em Linguagem M foram armazenadas no histórico.

Sempre a última etapa na lista de Etapas Aplicadas é o que consta no bloco IN.

Formatos na Linguagem M

Existem diversas formas diferentes de formatos e declarações na Power Query, no exemplo abaixo demostramos a forma de se declarar a data.

Para ajudar, disponibilizamos abaixo uma tabela contendo todos os formatos e declarações da Linguagem M na Power Query. Sempre que precisar de referências retorne nesta página e consulte esta tabela quantas vezes necessitar.

Trabalhando com Funções em Power Query

A Linguagem M é bastante funcional, é possível fazer quase tudo com ela. Para isto será necessário, em alguns casos, “chamar” funções.

Trabalhar com funções em Power Query é um pouco diferente de se trabalhar com as funções DAX e as fórmulas de excel. As diferenças não são grandes, a principal delas é que as letras maiúsculas e minúsculas afetam a efetividade das mesmas. Por exemplo, escrever tudo maiúsculo pode causar erro e não retornar o resultado esperado.

Veja o exemplo abaixo.

Esta função retorna o ano da variável “Data”. Observem que a função Date.Year tem duas letras maiúsculas, as que iniciam cada trecho, as outras são minúsculas. É assim que ela deve ser escrita, caso contrário pode apresentar erro no resultado.

Como listar as funções do Power Query no Power BI é um artigo aqui do blog que irá te ensinar como saber todas as funções disponíveis.

Comentários no código da Linguagem M

Como qualquer outra linguagem de programação, a M pode ser comentada também, o que pode ser feito de duas formas.

Em uma única linha de comentário, para isso utilize duas barras (//)

Ou em várias linhas entre barras e asteriscos (/* comentário */)

Um exemplo da vida real

Finalmente, após você receber estas noções básicas sobre a Linguagem M, apresentamos um exemplo, de um trabalho feito pela uaiSmart para analytics de Redes Sociais.

Veja que na imagem acima temos praticamente todos os itens que foram citados no artigo:

Os blocos let e in;
Nomes das variáveis;
Observer as hashtags e as aspas nos nomes das variáveis;
As linhas terminando com vírgula;
Funções;
Formatos;

Há outros diversos elementos que não falamos neste artigo, mas com certeza é ou será assunto de outros artigos aqui.

Recomendamos a leitura do artigo “Acrescentar Consultas vs Mesclar Consultas do Power BI” que fala mais sobre a Power Query.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

06/12/2017/0 Comentários/por Marcelo de Tarcio

Aprenda Power Query

Acrescentar Consultas vs Mesclar Consultas no Power BI

Combinar duas consultas do Power BI (o que explicamos aqui também se aplica no Power Query), é um dos passos mais básicos, mas também mais essenciais no processo de preparar dados para seus insights. Há duas formas de combinar as consultas: Acrescentar Consultas e Mesclar Consultas.

Experts em Base de dados sabem a diferença entre estes dois tipos, mas a maioria das pessoas que trabalham com Power BI e/ou Power Query não são programadores ou administradores de banco de dados. Neste post explicaremos a diferença entre Acrescentar Consultas e Mesclar Consultas. Explicaremos também quais as situações que devem utilizar um ou outro.

Por que combinar as consultas?

Provavelmente esta será a primeira pergunta que você fará. Porque exatamente tenho que combinar consultas?

A resposta é que dá para fazer a maior parte das tarefas em uma única consulta, mas será bem complicado e com centenas de passos. Assim a melhor saída, aquela mais esperta, dinâmica e mais rápida para o modelo, é combinar consultas. Com isso é possível ter consultas melhores e mais simples.

O resultado de combinar uma ou mais consultas é que gerará somente uma consulta no final. Esta conterá as colunas que lhe importam para criar o melhor modelo possível em seu trabalho.

Baixe o modelo em Power BI Desktop para acompanhar o passo a passo abaixo.

As opções Acrescentar Consultas e Mesclar Consultas são encontradas na faixa de opção Página Inicial na subseção Combinar, dentro do Editor de Consultas, conforme figura abaixo.

Acrescentar Consultas

Esta operação consiste em pegar os resultados de duas ou mais consultas, que podem ser cada uma delas uma tabela diferente, e transformar em uma só consultas contendo todos os resultados de cada uma das tabelas/consultas utilizadas no processo.

Em termos práticos os resultados do Acrescentar Consultas seriam:

Num exemplo, caso uma consulta tenha 50 linhas e a outra consulta tenha 100 linhas a operação criará uma nova consulta com 150 linhas;
No caso de colunas, estas se manterão na mesma quantidade, ou seja, se na primeira consulta contém col1, col2 …, col10, após acrescentar uma segunda consulta com as mesmas colunas o resultado será uma consulta com as mesmas colunas, sem repeti-las.

Vamos demostrar melhor por um exemplo. Aqui temos duas tabelas de Vendas.

Uma de Jan/17

Outra de Fev/17

Transformaremos estas duas consultas em uma, criando assim uma consulta com as vendas de todos os meses. Selecione a tabela “Vendas Jan17” (1), clique em Combinar e a opção Acrescentar Consultas (2) ficará visível.

Agora clique na setinha para abrir todas as possibilidades desta opção. Se quiser manter as consultas originais como estão e criar uma nova consulta no processo, escolha Acrescentar Consulta como Novas. Caso contrário escolha a opção Acrescentar Consultas para substituir as consultas atuais por uma nova.

No exemplo que estamos rodando para este artigo decidimos manter as consultas originais intactas, criando uma nova.

Você também escolhe qual será a tabela primária. Normalmente é a tabela que você selecionou com o clique antes de começar o processo (“Vendas Jan17”). As informações desta tabela virão primeiro e a cada consulta acrescentada os dados ficarão depois.

Escolha então as demais consultas a serem acrescentadas, no exemplo “Vendas Fev17” e clique OK.

Fizemos neste exemplo com duas tabelas, mas é possível fazer para quantas tabelas desejar.

O resultado final foi acrescentar as linhas da segunda tabela dentro da primeira tabela, conforme imagem abaixo.

Renomeie agora esta nova consulta para “Vendas”.

O processo se resume na figura a seguir, simples assim.

Caso tenha linhas com as mesmas informações nas consultas que foram juntadas ficará duplicado na nova consulta, para retirar as linhas duplicadas será necessário ou utilizar a opção Agrupar por ou a opção Remover linhas duplicadas para acabar com duplicidades.

E se as colunas de uma consulta não forem exatamente iguais nas outras consultas?

Para um melhor resultado esta operação requer que as colunas sejam iguais nas consultas. Mas caso não sejam é possível ainda realizar o processo. Cada coluna diferente será criada na nova consulta, mas com informações nulas nas células pertencentes à consulta que não tenha aquela coluna.

Mesclar Consultas

O Mesclar Consultas é outra forma de combinar dados que se baseia na combinação entre linhas ao invés de colunas. O resultado que acontece com a mesclagem depende de alguns fatores:

Deve haver uma coluna em comum entre as consultas, que possibilite a ação. Ex.: código do produto que deve estar em todas as consultas a serem mescladas.
O número de linhas dependerá da combinação de critérios entre as consultas.
O número de colunas dependerá de quais colunas selecionadas na configuração. O mesclar consultas irá criar uma estrutura de colunas como resultado.

Para um melhor entendimento deste conceito disponibilizamos o exemplo abaixo.

A tabela “Produto” será utilizada agora no exemplo, além da consulta criada “Vendas” no exemplo anterior.

Agora se quisermos combinar a consulta “Vendas” com a consulta “Produto” para saber quais os nomes dos produtos vendidos em cada uma das linhas. É necessário utilizar Mesclar Consultas. A consulta “Vendas” ficou assim, só para relembrar.

Selecione a consulta “Vendas” e em seguida clique em Combinar, clique na setinha de Mesclar Consultas e finalmente Mesclar Consultas como Novas.

Na caixa em branco (1) escolha qual consulta será mesclada e em seguida selecione a coluna que será a chave. Isto nas duas consultas a serem mescladas (2). Neste caso “Cod. Produto”.

Veja também na figura acima que a seleção fez correspondência em todas as linhas (3). Clique em OK para finalizar.

Sobre o Tipo de Junção veja com mais detalhe no infográfico em PDF que montamos para você entender de forma fácil e simples como funciona cada um dos tipos presentes no Power BI. Use a caixa abaixo para fazer o download deste infográfico.

[optin-cat id=”368″]

Como resultado será criado uma nova consulta, idêntica à consulta “Vendas”, mas com uma nova coluna chamada “Produto”.

Na nova coluna, está com a palavra “Table” em todas as linhas. Isto quer dizer que a tabela “Produto” não foi expandida e se encontra inteira dentro da coluna. Com isso o próximo passo é expandir a nova coluna. Na figura acima veja o ícone à direita do nome da coluna “Produto”. É por ele que se faz a expansão das colunas da tabela produto.

Clique no ícone de Expandir (1), na caixa que aparece terá à disposição todas as colunas que a tabela a ser mesclada oferece. Neste exemplo temos somente duas colunas para escolher e como o “Cod. Produto” já existe na tabela de “Vendas” a única coluna que nos interessa então é a “Nome Produto”. Portanto desmarque a coluna “Cod. Produto” (2) e clique em OK.

O resultado será uma nova coluna com as informações de nome do produto em cada uma das linhas, conforme segue.

As quatro primeiras colunas vêm da tabela “Vendas” e a última coluna vem da tabela “Produto”.

Com isto terminamos nossas explicações sobre as duas formas de combinar consultas no Power BI: Acrescentar Consultas e Mesclar Consultas. Não deixe de baixar o explicativo dos tipos de junção existentes em Mesclar Consultas. Assim poderá entender melhor estas opções também.

Compartilhe este conteúdo nas suas redes sociais (utilize os botões abaixo) e ajude seus colegas a compreender melhor sobre o assunto.

Tem dúvidas? Nos pergunte! Utilize a sessão de comentários deste artigo logo abaixo.

Até a próxima!

01/11/2017/0 Comentários/por Marcelo de Tarcio

Requisitos para iniciar a criação da tabela calendário

Construindo a tabela calendário

Contagem regressiva de dias úteis no Power BI – Criando Medidas

Web Scraping com Power BI – Extração dos dados

Consulta em Múltiplas Páginas no Power BI

Pode ser feito de 2 modos:

Possíveis erros durante o Web Scraping

Adicionando coluna condicional com Power Query

Mas como adicionar os operadores AND e OR?

1ª Forma: Ordem das Condições

2ª Forma: Concatenando as colunas

3ª Forma: Utilizando a Linguagem M

Pronto para criar coluna condicional no Power BI?

O que é API

Conectando uma API no Power BI

Mas como ter autorização?

Usando um conector de API no Power BI Desktop

Pastas

Funções

Utilizando as opções das Pastas

Utilizando uma função na API do Mailchimp

Utilize nomes que façam sentido na importação de dados no Power BI

Evite nomes repetidos ou parecidos nas colunas e medidas

Remova colunas desnecessárias na importação de dados no Power BI

Divida as colunas de data e hora

Aplique a opção “Marcar como tabela de data” em tabelas com datas

Conclusão

Extração, Transformação e Carregamento (o que é ETL)

Extração

Transformação

Carregamento

Considerações importantes para o entendimento sobre o que é ETL e para trabalhar melhor nos processos

Disponibilidade das fontes de Dados

Simplifique sua vida

Escalabilidade de dados

Conclusão

Passo a passo para listar as funções do Power Query

Convertendo lista em tabela

Documentação das Funções

Enumeradores

Conclusão

O que é Linguagem M

Estrutura (Sintaxe) da Linguagem M

Quebras das linhas de códigos

Nomes das variáveis

Cada etapa do Power Query

Formatos na Linguagem M

Trabalhando com Funções em Power Query

Comentários no código da Linguagem M

Um exemplo da vida real

Por que combinar as consultas?

Acrescentar Consultas

E se as colunas de uma consulta não forem exatamente iguais nas outras consultas?

Mesclar Consultas

uaiSmart Gestão Estratégica da Informação

Contato