CONCATENATEX em Power Query. A nova função TEXTJOIN é incrível. Você pode fazer a mesma coisa com o Power Query? Sim. Agora você pode.
Assistir vídeo
- Um visualizador baixa dados de um sistema onde cada item é separado por Alt + Enter
- Bill: Por que você está fazendo isso? Visualizador: É assim que herdo os dados. Eu quero manter dessa forma.
- Bill: O que você quer fazer com os 40% dos valores que não estão na tabela? Visualizador: sem resposta
- Bill: Existe uma maneira complicada de resolver isso se você tiver as ferramentas Power Query mais recentes.
- Em vez disso, uma macro VBA para resolvê-lo - a macro deve funcionar desde o Excel 2007
- Em vez de fazer VLOOKUP, faça uma série de Find & Replace with VBA
Transcrição de vídeo
Aprenda Excel com, Podcast Episódio 2151.
Eu realmente não sei como chamar esse aqui. Se estou tentando atrair as pessoas que usam DAX, diria ConcatenateX no Power Query, ou apenas as pessoas que usam Excel normal, mas o Office 365, diria TEXTJOIN no Power Query ou, para ser completamente honesto, é um conjunto supercomplexo de etapas no Power Query para permitir uma solução super-insana no Excel.
Ei. Bem-vindo de volta ao netcast. Sou Bill Jelen. Bem, ontem no episódio 2150, descrevi o problema. Alguém enviou este arquivo onde seu sistema está baixando os itens que são um pedido com feeds de linha entre eles. Em outras palavras, ALT + ENTER, e veja, WRAP TEXT está ativado, e eles querem fazer uma PROCV nesta LOOKUPTABLE para cada um desses itens. Eu sou assim o quê? Por que você está fazendo isso? Mas eu falei sobre isso ontem. Vamos apenas tentar descobrir como fazer isso.
Na verdade, eu disse, bem, o Power Query seria a melhor maneira de fazer isso, mas fiquei confuso sobre como fazer a última parte. Eu disse, está tudo bem se cada item terminar em sua própria linha? Não, eles têm que estar de volta na sequência original. Acho que é horrível, mas, no meu feed do Twitter na semana passada, Tim Rodman, 27 de setembro: “Finalmente lendo este livro,” - acho que é PowerPivot Alchemy - “e já realizou seu desejo ConcatenateX. ” Eu estava sendo um espertinho quando fiz isso, pedindo TALVEZ ROMANX, mas provavelmente realmente queria ConcatenateX, então Tim me avisou que agora posso fazer isso no Power BI.
Então, procurei meus amigos, Rob Collie do Power Pivot Pro e Miguel Escobar, e, você sabe, os dois são autores de grandes livros. Eu tenho esses dois livros, mas esse recurso é muito novo, não em nenhum dos livros. Eu disse, ei, vocês sabem fazer isso? E o Miguel ganha o prémio porque o Miguel se levantou cedo esta manhã ou ontem à noite - não sei bem qual - e enviou o código.
Tudo bem, então aqui está o plano no Power Query e este é tão complicado. Nunca escrevo um plano no Power Query. Eu só vou fazer as coisas todas. Vou começar com os dados originais, adicionar uma coluna INDEX para que possamos manter os itens de um pedido juntos, SPLIT COLUMN em ROWS usando um LINEFEED. Esta é a segunda ou terceira vez no podcast que uso esse novo recurso. Quão legal é isso. Eu tinha uma segunda coluna INDEX para que possamos classificar os itens na sequência original e, em seguida, SALVAR COMO UMA CONEXÃO.
Então, vamos para a tabela LOOKUP, torná-la uma tabela, consulta da tabela, SALVAR COMO CONEXÃO - essa seria a parte mais fácil ali - e então mesclar esta consulta e esta consulta com base no item número, todos os itens da tabela da esquerda, esta é a tabela da esquerda, combinando da direita, substitua os nulos pelo número do item. Ainda estamos no ar sobre o que queremos fazer quando algo não é encontrado por algum motivo. Eu fiz esta pergunta, mas a pessoa que enviou o arquivo não está respondendo, então vou apenas substituí-lo pelo número do item. Esperançosamente, a coisa certa a fazer é adicionar mais itens a LOOKUPTABLE para que não haja nenhum não encontrado, mas aqui estamos, e então vamos classificar por INDEX1 e INDEX2, dessa forma,as coisas voltaram à sequência certa e essa foi a parte que eu não consegui descobrir como fazer.
Vamos agrupar por INDEX1 fazendo o equivalente a um TEXTJOIN ou ConcatenateX com o caractere 10 como separador, como agregador e, claro, esta é a parte difícil, mas é a parte que é realmente nova aqui em este conjunto de etapas. Então, se você entende o que TEXTJOIN faz ou pode conceituar o que ConcatenateX teria feito, estamos essencialmente fazendo isso usando esse tipo de etapa. Então, tudo bem. Então, vamos tentar.
Então, vamos começar aqui. Aqui estão nossos dados originais, tem um título. Então, vou FORMATAR COMO TABELA, CONTROL + T, MINHA TABELA TEM CABEÇALHOS, sim, e então vamos usar o Power Query. Agora, estou no Excel 2016 Office 365, então está aqui na parte esquerda da guia DADOS. Se você estiver apenas no Excel 2016 puro, não no Office 365, está no meio - OBTER E TRANSFORMAR. Se você estiver no Excel 2010 ou 2013, terá sua própria guia aqui chamada Power Query, e se você não tiver essa guia, você terá que baixar essa guia. Se você estiver em um Mac ou Android ou qualquer uma das outras versões falsas do Excel, desculpe, não há Power Query para você. Obtenha uma versão do Excel para Windows e experimente.
Tudo bem, então, vamos fazer uma Power Query DE UMA TABELA, tudo bem, e a primeira coisa que vou fazer é ADICIONAR uma COLUNA DE ÍNDICE e vou começar DE 1. Tudo bem , então, este é essencialmente o pedido 1, pedido 2, pedido 3, pedido 4. Em seguida, vamos escolher esta coluna e, na guia TRANSFORMAR, vamos DIVIDIR COLUNA, POR DELIMITER, e eles foram capazes de detectar que é um LineFeed é o delimitador. Adoro que o Power Query esteja detectando isso. Agora, por que o Excel, texto em colunas, sim, texto em colunas, não descobre qual é o delimitador? E a cada ocorrência vamos DIVIDIR EM LINHAS e USAR CARÁTER ESPECIAL. Tudo bem, então tudo isso é bom.
Agora observe o que acontece aqui. Temos 999 linhas, mas agora temos muito mais do que isso. Portanto, cada item naquele número de pedido agora é sua própria linha. Agora, a pessoa que fez essa pergunta não quer que seja sua própria linha, mas vamos ter que fazer com que seja sua própria linha para que possamos fazer a junção. Vou adicionar uma nova coluna INDEX aqui. ADICIONE COLUNA, COLUNA DE ÍNDICE, DE 1, e então temos … esses são essencialmente os números do pedido e, em seguida, esses são a sequência dentro do pedido, porque eu determinei que, mais tarde, eles estarão em alguma outra ordem. Não sei para que ordem eles mudam, mas aqui estamos.
Tudo bem, então, HOME, não o botão CLOSE & LOAD, mas a lista suspensa CLOSE & LOAD e CLOSE & LOAD TO. Não sei por que leva 10 segundos para eles exibirem esta caixa de diálogo pela primeira vez. Vamos SOMENTE CRIAR CONEXÃO. Clique OK. Bonito. Então essa é a TABELA1, TABELA1.
Agora, vamos para nosso LOOKUPTABLE. LOOKUPTABLE vai ser fácil de processar. Vamos formatar isso como uma tabela. CONTROL + T. Clique OK. DATA, ou POWER QUERY se você estiver em uma versão antiga, FROM TABLE. Isso vai se chamar TABLE2. Vamos chamá-lo de LOOKUPTABLE. Perfeito. CLOSE & LOAD, CLOSE & LOAD TO, SÓ CRIA A CONEXÃO.
Tudo bem. Agora, temos nossos dois bits aqui e quero mesclar esses dois. Então, vamos apenas para um novo local e, em seguida, DATA, GET DATA, COMBINE QUERIES, vamos fazer um MERGE, e a tabela à esquerda será TABLE1 - esses são os nossos dados originais - - e vamos usar este número de ITEM e vamos casar até o LOOKUPTABLE e esse número de ITEM. É realmente não intuitivo lá você tem que clicar nos ITENS em ambos os casos para definir qual é a chave, e uma junção OUTER, ALL FROM FIRST, MATCHING FROM SECOND, e, veja, há 40% destes que estão faltando no TABELA DE PESQUISA. Todos esses dados são falsos, mas os dados originais também tinham 40% faltando no LOOKUPTABLE. Realmente meio frustrante. Tudo bem. Então, aqui está nosso número de ITEM, nossos 2 campos INDEX e, em seguida, nosso LOOKUPTABLE aqui. EU'Vou EXPANDIR isso e pedir a DESCRIÇÃO. Tudo bem, você vê que temos um monte de nulos aqui.
Tudo bem, então, vamos fazer uma coluna condicional. A coluna condicional dirá olhe para esta coluna. Se for = para nulo, traga esse valor, caso contrário, use o valor que está nessa coluna. Então aqui, sob ADICIONAR COLUNA, faremos CONDITIONAL COLUMN - uma bela IU que nos guiará por isso - se LOOKUPTABLEDESCRIPTION É IGUAL A NULL, então queremos usar uma COLUNA aqui de ITENS, caso contrário, queremos usar a COLUNA chamada LOOKUPDESCRIPTION, certo. Clique em OK e aqui estamos. Há nossa coluna CUSTOM com o novo valor de LOOKUPTABLE ou o valor original se não for encontrado. Neste ponto, podemos clicar com o botão direito e dizer que queremos REMOVER esta coluna. Era uma coluna temporária, era uma coluna auxiliar. Agora que temos o que precisamos, não precisamos mais dessa coluna e, na verdade, neste ponto,Eu também não preciso mais dessa coluna. Então, posso clicar com o botão direito e REMOVER essa coluna. Tudo bem. Agora, temos nossos dados aqui. Quero classificá-lo pelo INDEX original. Então, SORT ASCENDING. Isso coloca nossos dados na sequência certa e, agora que está classificado, posso clicar com o botão direito e REMOVER essa coluna.
Alright. Now we’re at the point where, for every item, each order number -- so, this is order number 1, let's say -- I want to have these 4 items separated by a LineFeed character. Now, what I was hoping to be able to do was to come here to TRASNFORM. Instead, if we wanted to GROUP BY and that there'd be some magic here in the GROUP BY, I would say I'm going to concatenate or textjoin all those things, but it doesn't work, alright?
So, here's the set of steps that are new to me that allow this to happen. First thing we want to do is we're going to create a brand new column. That column is just going to be called a TABLECOLUMN and we're going to take ALL ROWS and click OK. Okay. So, when we look inside at this table, we see that we have 2 columns -- one called INDEX and one called CUSTOM -- and we have to remember that name there, alright, and this table unfortunately does not work with STRUCTURED COLUMN. See, EXTRACT VALUES is grayed out. So, this doesn't work with a table. It has to work with a list. I have to convert this table to a list, and this is the part I couldn't figure out and the part that Miguel filled in for me.
So, I'm going to create a CUSTOM COLUMN here and I'm going to call it a LISTCOLUMN and we're going to use a function called TABLE.COLUMN and the table is the thing called TABLECOLUMN, and then which column in there is the thing called CUSTOM. Click OK. Alright, and now these are, instead of a table, it is a list. We're home-free now. TRANSFORM, STRUCTURED COLUMN. I'm going to EXTRACT VALUES. I'm going to create a CUSTOM delimiter USING SPECIAL CHARACTERS, INSERT SPECIAL CHARACTER, LINE FEED, and click OK, and it gives me what I'm looking for. So here’s my original order number. The TABLE, we don’t need anymore, right click and REMOVE that, and we now have our original data using the LOOKUPTABLE where we need it, alright? So, I can right-click and REMOVE this, alright, and then finally, HOME, just straight CLOSE & LOAD, which brings it back into a table in Excel. (=Table.Column((TableColumn),“Custom”))
Alright, but it doesn't look like it worked, does it? That's because, by default, this table does not have WRAP TEXT turned on. So, HOME, WRAP TEXT, and we now have our new data doing the equivalent of a VLOOKUP for each item in the list, and when an item is not found, the original item number is still there, so someone can go piece that back together.
Now, the beautiful thing with Power Query is that while it took us some time to get this set up the first time, the next time we download this list, we just copy it here, and we can even edit something. So, let’s change one. So, MANGO, 4954, we’ll take that 7036 and change it to 4954. Alright, so, now the underlying data has changed, all we have to do is come here to this and click the REFRESH which will refresh all of these items, and we get here to SHEET11 and that second item has changed to a MANGO, alright? It’ll take you some time to set this up once but, once you get it set up, it's just a simple matter of refreshing the data and Power Query will go through all those steps.
Agora, ei, este é o ponto onde eu costumo pedir que você vá comprar meu livro, mas, hoje, vamos pedir que você vá comprar o livro de Miguel. Miguel Escobar e Ken Puls escreveram este excelente livro sobre M Is For (DATA) MONKEY - o melhor livro que existe sobre Power Query. Vá verificar isso.
Tudo bem, para encerrar: hoje é um episódio muito longo; temos um visualizador, baixa os dados de um sistema onde cada item é separado por ALT + ENTER e estamos tentando fazer um VLOOKUP para cada item individual; construiu uma solução hoje usando o Power Query, incluindo a ferramenta de coluna estruturada de extrair como; mas isso só funciona em uma lista, não em uma tabela, então tive que usar a função TABLE.COLUMN para converter a tabela em uma lista.
Bem, ei. Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2151.xlsm
