Anexar várias planilhas no Excel ao LuckyTemplates

Este tutorial discutirá como importar e abrir um arquivo do Excel com várias planilhas em uma tabela do LuckyTemplates.

Este arquivo Excel de dados será usado para este tutorial.

O arquivo contém 60 folhas de dados com o mesmo layout.

O objetivo deste tutorial é carregar todas as planilhas do Excel no LuckyTemplates e recuperar as informações do Bond como uma tabela. Você precisa obter o ticker de segurança e adicioná-lo em uma coluna separada. Em seguida, anexe todos eles em uma única tabela que aproveita a interface do usuário para gerar o código de que você precisa.

Índice

• Importando as planilhas do Excel para o LuckyTemplates
• Trazendo o Security Ticker do Excel para o LuckyTemplates
• Removendo Linhas Vazias
• Duplicando uma consulta
• Atribuindo tipos de dados a colunas
• Adicionando coluna de ticker de segurança do Excel ao LuckyTemplates
• Criando uma coluna personalizada para consulta de títulos
• Conclusão

Importando as planilhas do Excel para o LuckyTemplates

A primeira coisa a fazer é abrir o Editor do Power Query e criar um parâmetro para armazenar a localização do arquivo usando a opção Gerenciar Parâmetros .

Dentro da caixa Gerenciar parâmetros, clique em Novo e insira FileLocation como o nome do parâmetro. Em seguida, defina o Tipo de dados como Texto e os Valores sugeridos como Qualquer valor . Copie o caminho do arquivo do Excel e cole-o nos campos Valor atual.

Depois de pressionar OK, clique em New Source e selecione Excel Workbook .

Em seguida, selecione e abra o arquivo Excel. Feito isso, a tela do Navegador será aberta e listará todas as planilhas dentro do arquivo Excel.

Selecione a primeira folha e pressione OK. Depois disso, você verá a tabela dentro do Editor do Power Query.

A próxima coisa a fazer é alterar o caminho do arquivo codificado para o parâmetro de arquivo. Abra a janela do Editor avançado e altere o caminho do arquivo no código-fonte para FileLocation .

É melhor ter seus dados em tabelas do Excel porque os limites ou o intervalo de dados são definidos antes de trazê-los para o Power Query . Como o arquivo contém planilhas e não tabelas, ele corre o risco de trazer colunas e linhas vazias, então você precisa resolver isso.

Trazendo o Security Ticker do Excel para o LuckyTemplates

Saber que o layout das planilhas é fixo ajuda na construção de uma solução, principalmente se você deseja maximizar e utilizar a interface do usuário para gerar o código. Por exemplo, um requisito é adicionar uma coluna que contenha o ticker de segurança. Se você observar os dados, poderá ver o ticker.

A interface do usuário pode ajudá-lo a extrair o valor do ticker. Clique com o botão direito do mouse na célula e selecione Adicionar como nova consulta .

Na barra de fórmulas, você verá que o nome da tabela é seguido por um número de linha baseado em zero entre chaves. Os colchetes são chamados de operadores de índice posicional . Você também verá o nome do campo entre colchetes que são chamados de operadores de acesso de campo .

Com esses códigos, agora você pode extrair o valor. Volte para a tabela Bond e obtenha os dados Bond. Primeiro, remova as oito primeiras linhas. Clique em Remover linhas e selecione Remover linhas superiores .

Em seguida, insira 8 no campo Number Of Rows e pressione OK.

Em seguida, clique em Usar primeira linha como cabeçalhos para definir os cabeçalhos.

Uma vez feito isso, a tabela Bond ficará assim.

Logo abaixo do cabeçalho, você verá uma linha fina que representa a qualidade da coluna. A partir daí, você pode ver que há um grande número de espaços em branco nas colunas. Isso significa que o arquivo trouxe muitas linhas vazias.

Removendo Linhas Vazias

Para remover as linhas vazias, clique em Remover linhas e selecione Remover linhas em branco .

Essa transformação gera esta sintaxe:

Record.FieldValues ​​está obtendo todos os valores da linha atual na tabela como uma lista. List.RemoveMatchingItems remove todos os valores na primeira lista que têm uma correspondência na segunda lista. A segunda lista contém apenas uma string de texto vazia ou nula. Esses são os valores que serão excluídos da primeira lista.

Se todas as strings de texto vazias e null tiverem sido removidas da lista com os valores do campo de registro, a lista deverá estar vazia e List.IsEmpty será avaliado como True. Em seguida, Table.SelectRows manterá os Trues.

Você não deve terminar com uma tabela apenas com linhas em branco. É por isso que a palavra-chave not é adicionada antes de List.IsEmpty . Isso retorna uma tabela contendo linhas não em branco.

Além das linhas em branco, você também precisa remover as colunas em branco. Mas antes disso, dê uma olhada no que o Power Query gera quando você remove uma coluna. Selecione a quarta coluna e clique em Remover colunas .

Após realizar a transformação, você verá esta sintaxe na barra de fórmulas.

Ele chama a função Table.RemoveColumns e, em seguida, faz referência e passa a etapa anterior no painel Etapas aplicadas como primeiro argumento. A transformação passa ainda uma lista contendo o nome da coluna das colunas que você deseja remover.

Duplicando uma consulta

Agora, duplique a consulta e selecione a etapa Promover cabeçalhos no painel Etapas aplicadas. Em seguida, clique com o botão direito do mouse nessa etapa e selecione Excluir até o fim .

Lembre-se de que você pode usar o operador de índice posicional para passar um número de linha baseado em zero em um conjunto de chaves. Portanto, mantenha apenas a primeira linha inserindo 0 entre duas chaves na barra de fórmulas.

Uma vez feito, a tabela Bond ficará assim.

A partir daí, reutilize uma parte da lógica que foi criada pela etapa Removed Blank Rows para transformar o registro em uma lista e remover o nulo. Volte para a consulta Bond e selecione a etapa Remove Blank Rows. Em seguida, copie este código M.

Volte para a consulta duplicada e cole o código na barra de fórmulas. Em seguida, organize algumas strings para que a fórmula fique assim.

Agora você criou uma lista com os nomes das colunas que deseja manter. Renomeie a consulta Nomes de coluna .

Em seguida, volte para a consulta de Bond. Como você criou uma consulta que contém todas as colunas que deseja manter, é necessário listar todas as colunas que deseja excluir na função Table.RemoveColumns .

Altere {Column4} com a mesma sintaxe que foi copiada nas etapas Removed Blank Rows. Altere também {“”, null} para ColumnNames .

Então, você precisa passar uma lista com os nomes das colunas reais da tabela Bond. Altere Record.FieldValues(_) para Table.ColumnNames() . Insira #”Removed Blank Rows” dentro dos parênteses para passar um argumento de referência de tabela.

Atribuindo tipos de dados a colunas

A próxima coisa a fazer é atribuir os tipos de dados apropriados às colunas. Para a coluna Data, clique no ícone ao lado do cabeçalho e selecione Data.

Para as colunas PX_LAST e YLD_YTM_MID, selecione o tipo de dados Decimal Number.

Com essas três consultas, você criou todos os blocos de construção que podem ser usados ​​para projetar uma solução que aborde todas as planilhas do arquivo Excel . Para isso, você precisa duplicar a consulta Bond e excluir todas as etapas, exceto a etapa Source no painel Applied Steps.

Adicionando coluna de ticker de segurança do Excel ao LuckyTemplates

Na etapa Fonte, você pode ver todos os dados dentro do arquivo Excel. Em vez de criar uma consulta separada para cada planilha, você pode usar a consulta Bond e transformar as tabelas aninhadas na coluna Dados.

Primeiro, adicione o ticker de segurança. Se você clicar no espaço em branco dentro da célula de qualquer tabela, verá uma visualização do conteúdo dessa tabela.

Você precisa criar uma lógica usando uma instrução para obter o ticker de segurança. Vá para a consulta da Coluna2 e copie o endereço do ticker de segurança da barra de fórmulas.

Em seguida, volte para a consulta Bond e adicione uma coluna personalizada.

Nomeie a coluna Security Ticker e escreva o seguinte código M.

A fórmula possui uma instrução IF informando que, se a palavra Segurança for encontrada na Coluna1, ela fornecerá o valor da célula da Coluna2 adjacente a ela. Caso contrário, um nulo será fornecido.

Após pressionar OK, uma nova coluna com os tickers de Segurança será adicionada à tabela.

Clique no botão suspenso da coluna Security Ticker e desmarque nulo para remover todos os nulos na coluna.

Depois disso, você ficará com todas as informações dos Títulos de todas as planilhas. Basta repetir as transformações realizadas na consulta Bond(2) e aplicá-las nas tabelas aninhadas na coluna Dados.

Criando uma coluna personalizada para consulta de títulos

Vá para a consulta Bond(2), abra a janela do Editor Avançado e copie o seguinte código:

Em seguida, volte para a consulta Bond e crie outra coluna personalizada. Como você precisa aplicar várias transformações em várias etapas, é necessário usar uma instrução let . Então, insira deixe e cole o código do Editor Avançado.

Em seguida, altere o Bond_Sheet para Data para transformar a tabela na coluna Data.

Após pressionar OK, uma nova coluna será adicionada à tabela.

Se você clicar no espaço em branco dentro de qualquer célula dessa coluna, verá os dados da consulta Bond(2).

Tudo que você precisa está nas duas últimas colunas da consulta Bond. Portanto, selecione as colunas Security Ticker e Custom e clique em Remove Other Columns na faixa Remove Columns.

Expanda a coluna Custom e desmarque a caixa Use The Original Name As Prefix.

Feito isso, valide a tabela. Abaixo da tabela, selecione a opção Criação de perfil de coluna com base em todo o conjunto de dados .

Após essa transformação, ocorrerá um erro na coluna Dados.

Portanto, examine o erro clicando na coluna Dados e selecionando Manter Erros na faixa Manter Linhas.

Se você clicar no valor Error dentro da coluna Data, poderá ler a mensagem de erro abaixo:

Para resolver os problemas, primeiro remova a etapa Erros mantidos. Clique na coluna Dados e selecione Remover Erros na faixa Remover Linhas.

Em seguida, defina a opção de criação de perfil da coluna de volta para as 1.000 primeiras linhas. E é isso!

Conclusão

Essa solução orientada à interface do usuário pode ajudá-lo a anexar várias planilhas de um arquivo do Excel ao LuckyTemplates. Em vez de criar 60 consultas separadas e executar todas as transformações repetidamente, esta solução pode criar uma única consulta que executa todas as transformações. Utilize e maximize esta solução para criar um excelente relatório de dados .

melissa