O Power Query é muito poderoso. Mas estou prestes a fazer o Power Query Squared … escrever uma consulta para uma página e depois fazer com que o Excel execute a mesma consulta para uma lista inteira de páginas da web.
Assistir vídeo
- O truque de hoje foi adaptado do livro M is for Data Monkey
- Crie uma consulta para obter dados de uma página da web
- Edite a consulta para transformá-la em uma função com (VariableName) => antes de Let
- Altere o URL codificado para VariableName
- Renomeie a consulta para fxWeather
- Fechar e carregar. Os dados desaparecerão.
- Use o Excel Trickery para criar uma tabela de todos os URLs
- Crie uma consulta a partir dessa tabela.
- Adicionar uma nova coluna de clima
=fxWeather((URL)) - Expanda a coluna. Desmarque o prefixo
- Surpreendente!
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2056: Power Query Squared
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Eu descobri esse truque enquanto me preparava para fazer um seminário em uma conferência em Dallas chamado Excelapalooza, o maior nome de conferência do Excel de todos os tempos. Você deve conferir todo mês de setembro em Dallas.
E crédito para Ken Puls e Miguel Escobar porque eu tive que fazer uma hora sobre Power Query, então é claro, eu peguei lá um livro incrível, o maior livro do mundo sobre Power Query. Eu estava folheando o livro e vi que eles tinham uma técnica e disse: “Espere um pouco. Vou ver se consigo adaptar essa técnica. ” E isso para mim é incrível em termos de consulta de energia. E aqui está o que vamos fazer, vamos pegar uma consulta avançada e depois executar essa consulta avançada, aquela consulta, dezenas de vezes, certo?
E então, o exemplo que eu criei foi onde eu queria extrair dados de uma página da web, certo. E eu fui - eu apenas procurei algumas páginas da web que eu pudesse - que eu pudesse usar como exemplo. Acabei no Weather Underground e aqui está o URL, e você pode ver que eu estava em Dallas-Fort Worth, então estamos extraindo dados para Dallas e parece que é 2 de janeiro de 2015. Bem ali no URL estão os parâmetros , certo? E esse é um URL perfeito para fazer isso funcionar.
Vamos dar uma olhada rápida na página da web, embora não seja tão importante. Você vê, há muitos dados diferentes aqui na página da web e eu decidi que iria apenas tentar obter precipitação e temperaturas altas e baixas. E aqui está essa consulta. E deixe-me dizer aqui que este podcast não é sobre como criar esta consulta, nova consulta, de outras fontes, da web, especificar o URL e, em seguida, um monte de etapas que não vou detalhar aqui para obter meu resposta final de Temp. máx., Temp. mín. e precipitação. A questão é que você pegará sua própria consulta e a fará funcionar para um monte de coisas.
Então, clico em Fechar e carregar e essa consulta está funcionando, está retornando minha única linha. Tudo é fantástico. E vou voltar, vou Editar esta consulta e vou para Exibir, Editor avançado. Vou pegar essa consulta e torná-la uma função, certo? Então, bem aqui antes da palavra LET, eu pressiono Enter. E entre parênteses, vou dar uma variável (MyURL) e então => flecha aqui, certo? Legal. E então aqui embaixo, onde eles têm a URL entre aspas, eu quero me livrar de toda a URL, incluindo as aspas e, em seguida, digitar meu nome de variável MyURL, certo. Então, o que estamos dizendo é que vamos passá-lo para URL e ele fará a mesma consulta, mas com qualquer URL que passarmos.
Agora, algumas coisas desconcertantes aqui, quando clico em Concluído, oh cara! Todas as minhas etapas aplicadas se foram e eles querem que eu insira um parâmetro. Apenas ignore tudo isso. Vamos renomear isso; vamos chamá-lo de fxWeather. FX, é claro, sendo a abreviatura de função e você realmente tem que lembrar esse nome e lembrar quais letras são maiúsculas, isso será muito importante em alguns minutos. Home, Close & Load e BAM! Tudo se foi. Ah não! Mas está tudo bem. Tudo bem, então sabemos que está aí. É apenas uma conexão. Agora, eu vou vir aqui e este é apenas o Excel antigo, certo? Então aqui está a URL, dividi-a na primeira parte da URL, a parte final da URL. Eu sei que preciso marcar a data; Preciso formatá-lo neste formato estranho de ano,mês e dia, então usei a função TEXT para fazer isso. Coloque a data de início aqui. Posso até mudar o aeroporto, agora estou indo e voltando. Vamos fazer MCO para Orlando e vamos fazer alguns dados recentes. Vou começar em 01/10/2016, tudo bem. Portanto, agora temos esta grande mesa montada aqui. E, a propósito, tem que ser uma mesa. Você deve usar o formato como tabela ou Ctrl + T. Então, você sabe, isso é apenas pegar essa data e, em seguida, + 1 + 1 + 1. Eu formato, eu construo o URL.Você deve usar o formato como tabela ou Ctrl + T. Então, você sabe, isso é apenas pegar essa data e, em seguida, + 1 + 1 + 1. Eu formato, eu construo o URL.Você deve usar o formato como tabela ou Ctrl + T. Então, você sabe, isso é apenas pegar essa data e, em seguida, + 1 + 1 + 1. Eu formato, eu construo o URL.
Tudo bem agora, vamos construir uma consulta a partir desta tabela. Tudo bem, e aqui estão minhas informações. Vou adicionar uma nova coluna, adicionar uma coluna personalizada, as colunas seriam chamadas de clima e a fórmula seria = fxWeather. Certifique-se de que é exatamente o mesmo caso, as mesmas letras maiúsculas e minúsculas, e vamos inserir aquele campo chamado URL assim, fechando parênteses. Sem erros de sintaxe, clique em OK. Eles querem saber sobre privacidade aqui, tudo isso são dados públicos, clique em Salvar, certo. Então, aí está o nosso encontro. É engraçado que eles mudaram meu formato para algo que não se parece com o que eu comecei. E aqui está o clima com o símbolo de expansão. Então, clicarei no símbolo de expansão, desmarque Usar nome da coluna original como prefixo. Eu quero o Max, o Min, a Precipitação,Clique OK. Tudo bem, e agora tudo que eu preciso é a data e as informações lá fora. Então, vou clicar com o botão direito e remover esta coluna, clicar com o botão direito e remover esta coluna. Por aqui, não preciso desse tempo, então direi que é apenas um encontro, certo. E ver o que está fazendo a cada data que estou passando; está retornando o alto, o baixo e a precipitação para Orlando. Cada linha aqui está indo para uma página da web diferente. Imagine só, se não fossem 15 linhas, mas 5.000 linhas, você configuraria para funcionar durante a noite. Eu costumava escrever macros para isso. Na verdade, uma das páginas da web em é como construir uma macro para copiar páginas da web de milhares de páginas da web diferentes em um site, não é mais necessário com consulta avançada.Clique com o botão direito e remova esta coluna, clique com o botão direito e remova esta coluna. Por aqui, não preciso desse tempo, então direi que é apenas um encontro, certo. E ver o que está fazendo a cada data que estou passando; está retornando o alto, o baixo e a precipitação para Orlando. Cada linha aqui está indo para uma página da web diferente. Imagine só, se não fossem 15 linhas, mas 5.000 linhas, você configuraria para funcionar durante a noite. Eu costumava escrever macros para isso. Na verdade, uma das páginas da web em é como construir uma macro para copiar páginas da web de milhares de páginas da web diferentes em um site, não é mais necessário com consulta avançada.Clique com o botão direito e remova esta coluna, clique com o botão direito e remova esta coluna. Por aqui, não preciso desse tempo, então direi que é apenas um encontro, certo. E ver o que está fazendo a cada data que estou passando; está retornando o alto, o baixo e a precipitação para Orlando. Cada linha aqui está indo para uma página da web diferente. Imagine só, se não fossem 15 linhas, mas 5.000 linhas, você configuraria para funcionar durante a noite. Eu costumava escrever macros para isso. Na verdade, uma das páginas da web em é como construir uma macro para copiar páginas da web de milhares de páginas da web diferentes em um site, não é mais necessário com consulta avançada.s retornando a alta, a baixa e a precipitação para Orlando. Cada linha aqui está indo para uma página da web diferente. Imagine só, se não fossem 15 linhas, mas 5.000 linhas, você configuraria para funcionar durante a noite. Eu costumava escrever macros para isso. Na verdade, uma das páginas da web em é como construir uma macro para copiar páginas da web de milhares de páginas da web diferentes em um site, não é mais necessário com consulta avançada.s retornando a alta, a baixa e a precipitação para Orlando. Cada linha aqui está indo para uma página da web diferente. Imagine só, se não fossem 15 linhas, mas 5.000 linhas, você configuraria para rodar durante a noite. Eu costumava escrever macros para isso. Na verdade, uma das páginas da web em é como construir uma macro para copiar páginas da web de milhares de páginas da web diferentes em um site, não é mais necessário com consulta avançada.
Now, when I Close & Load, it's funny the preview here is showing me all the results. When I Close & Load, they're actually going to go do each query. And so right now we have preview rows loaded and it will take a good long time for this information to get updated. So, go to lunch, do something especially if you're doing more than 15 rows. And it's funny the preview is correct but they're still going out and could chunk, could chunk, could chunking through each individual row.
And there it's loaded. Is this amazing or what? Hey, I do a lot of Excel seminars, the Power Excel seminar in Orlando, Florida. Look at these beautiful temperatures we have down here on November 4, 2016. My afternoon will be all about Power Query, Power BI, Power Pivot, Power Map. So, I'm going to invite you down to Orlando to check out this seminar. There'll be a link there in the top-right hand corner.
Alright, so recap. Today's trick is from this book, M is for (DATA) MONKEY. We built a query to get one web page and then edit that query to change it into a function. So right before the Let statement variable name => and then change the hard-coded URL to whatever that variable name is. Rename the query to fxWeather, Close & Load, the data disappears. Then, we use some sort of Excel trickery to create a table of all the URLs we want to crawl, create a query from that table. This has to be a Ctrl+T table, add a new column of Weather = fxWeather and again it has to match the case there, (URL), Expand that column, uncheck Prefix. BAM! It is amazing.
Thanks to Ken and Miguel for writing this book. Thanks to you for stopping by. Hope to see you in Orlando on November 4th 2016. See you next time for another netcast from.
Download File
Baixe o arquivo de amostra aqui: Podcast2056.xlsm
