Conteúdo
Se você já começou a usar as ferramentas do suplemento Power Query gratuito no Microsoft Excel, muito em breve você encontrará um problema altamente especializado, mas muito frequente e irritante, associado à quebra constante de links para dados de origem. A essência do problema é que, se em sua consulta você se referir a arquivos ou pastas externas, o Power Query codifica permanentemente o caminho absoluto para eles no texto da consulta. Tudo funciona bem no seu computador, mas se você decidir enviar um arquivo com uma solicitação para seus colegas, eles ficarão desapontados, porque. eles têm um caminho diferente para os dados de origem em seus computadores e nossa consulta não funcionará.
O que fazer em tal situação? Vejamos este caso com mais detalhes com o exemplo a seguir.
Formulação do problema
Suponha que temos na pasta E:Relatórios de vendas encontra-se o arquivo Os 100 principais produtos.xls, que é um upload do nosso banco de dados corporativo ou sistema ERP (1C, SAP, etc.) Este arquivo contém informações sobre os itens de commodities mais populares e se parece com isso:
Provavelmente está claro logo de cara que é quase impossível trabalhar com isso no Excel desta forma: linhas vazias através de uma com dados, células mescladas, colunas extras, um cabeçalho de vários níveis etc.
Portanto, ao lado deste arquivo na mesma pasta, criamos outro novo arquivo Manipulador.xlsx, na qual criaremos uma consulta do Power Query que carregará dados feios do arquivo de upload de origem Os 100 principais produtos.xls, e coloque-os em ordem:
Fazendo uma solicitação para um arquivo externo
Abrindo o arquivo Manipulador.xlsx, selecione na guia Data Command Obter dados – do arquivo – da pasta de trabalho do Excel (Dados — Obter dados — Do arquivo — Do Excel), especifique o local do arquivo de origem e a planilha de que precisamos. Os dados selecionados serão carregados no editor do Power Query:
Vamos trazê-los de volta ao normal:
- Excluir linhas vazias com Home — Excluir linhas — Excluir linhas vazias (Início — Remover Linhas — Remover Linhas Vazias).
- Exclua as 4 principais linhas desnecessárias Página inicial — Excluir linhas — Excluir linhas superiores (Início — Remover Linhas — Remover Linhas Superiores).
- Eleve a primeira linha ao cabeçalho da tabela com o botão Use a primeira linha como cabeçalhos aba INÍCIO (Home — Use a primeira linha como cabeçalho).
- Separe o artigo de cinco dígitos do nome do produto na segunda coluna usando o comando coluna dividida aba Transformação (Transformar - Coluna Dividida).
- Exclua as colunas desnecessárias e renomeie os títulos das restantes para melhor visibilidade.
Como resultado, devemos obter a seguinte imagem muito mais agradável:
Resta fazer o upload desta tabela enobrecida de volta para a planilha em nosso arquivo Manipulador.xlsx a equipe fechar e baixar (Início - Fechar e Carregar) aba INÍCIO :
Encontrando o caminho para um arquivo em uma solicitação
Agora vamos ver como nossa consulta se parece “nos bastidores”, na linguagem interna incorporada ao Power Query com o nome conciso “M”. Para fazer isso, volte para nossa consulta clicando duas vezes nela no painel direito Solicitações e conexões e na aba Avaliações escolher Editor Avançado (Visualizar — Editor Avançado):
Na janela que se abre, a segunda linha revela imediatamente um caminho codificado para nosso arquivo de upload original. Se pudermos substituir essa string de texto por um parâmetro, variável ou um link para uma célula de planilha do Excel onde esse caminho é pré-escrito, podemos alterá-lo facilmente mais tarde.
Adicionar uma tabela inteligente com um caminho de arquivo
Vamos fechar o Power Query por enquanto e retornar ao nosso arquivo Manipulador.xlsx. Vamos adicionar uma nova planilha vazia e fazer uma pequena tabela “inteligente” nela, na única célula na qual o caminho completo para nosso arquivo de dados de origem será escrito:
Para criar uma tabela inteligente a partir de um intervalo regular, você pode usar o atalho de teclado Ctrl+T ou botão Formatar como uma tabela aba INÍCIO (Início — Formatar como Tabela). O cabeçalho da coluna (célula A1) pode ser absolutamente qualquer coisa. Observe também que, para maior clareza, dei um nome à tabela parâmetros aba Construtor (Desenhar).
Copiar um caminho do Explorer ou mesmo inseri-lo manualmente não é particularmente difícil, mas é melhor minimizar o fator humano e determinar o caminho, se possível, automaticamente. Isso pode ser implementado usando a função de planilha padrão do Excel CELL (CÉLULA), que pode fornecer várias informações úteis sobre a célula especificada como argumento – incluindo o caminho para o arquivo atual:
Se assumirmos que o arquivo de dados de origem sempre está na mesma pasta que nosso Processador, o caminho que precisamos pode ser formado pela seguinte fórmula:
=LEFT(CELL("nome do arquivo");FIND("[";CELL("nome do arquivo"))-1)&"100 principais produtos.xls"
ou na versão em inglês:
=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»
… onde está a função LEVSIMV (DEIXOU) pega um pedaço de texto do link completo até o colchete de abertura (ou seja, o caminho para a pasta atual), e então o nome e a extensão do nosso arquivo de dados de origem são colados nele.
Parametrizar o caminho na consulta
O último e mais importante toque permanece - para gravar o caminho para o arquivo de origem na solicitação Os 100 principais produtos.xls, referindo-se à célula A2 da nossa tabela “inteligente” criada parâmetros.
Para fazer isso, vamos voltar para a consulta do Power Query e abri-la novamente Editor Avançado aba Avaliações (Visualizar — Editor Avançado). Em vez de um caminho de string de texto entre aspas “E:Relatórios de vendasOs 100 principais produtos.xlsx” Vamos introduzir a seguinte estrutura:
Excel.CurrentWorkbook(){[Name="Settings"]}[Conteúdo]0{}[Caminho para os dados de origem]
Vejamos em que consiste:
- Excel.CurrentWorkbook() é uma função da linguagem M para acessar o conteúdo do arquivo atual
- {[Name="Settings"]}[Conteúdo] – este é um parâmetro de refinamento para a função anterior, indicando que queremos obter o conteúdo da tabela “inteligente” parâmetros
- [Caminho para os dados de origem] é o nome da coluna na tabela parâmetrosa que nos referimos
- 0{} é o número da linha na tabela parâmetrosdo qual queremos obter dados. A tampa não conta e a numeração começa do zero, não do um.
Isso é tudo, na verdade.
Resta clicar em Acabamento e confira como funciona nosso pedido. Agora, ao enviar a pasta inteira com os dois arquivos dentro para outro PC, a solicitação permanecerá operacional e determinará o caminho para os dados automaticamente.
- O que é o Power Query e por que ele é necessário ao trabalhar no Microsoft Excel
- Como importar um trecho de texto flutuante para o Power Query
- Redesenhando uma crosstab XNUMXD para uma tabela plana com o Power Query