Tutorial do Microsoft Power Query sobre como corrigir problemas mistos de largura de coluna fixa

Mudassir: Por hoje, temos um problema muito interessante para trabalhar. O problema desse arquivo é que ele é fixo delimitado por colunas e não sei como resolver isso usando o Microsoft Power Query. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Primeiro, não foi fácil para mim excluir as colunas dinamicamente. Em segundo lugar, neste relatório, temos uma tabela com largura de coluna diferente e outra tabela com largura de coluna diferente.

Portanto, se eu aplicar um delimitador dinamicamente na parte superior, não conseguirei obter os dados de forma organizada. Quero obter todos esses dados da segunda tabela e os números dos meus produtos da primeira tabela. Eu também quero o número do trabalho em cada linha de todas as tabelas.

Tentei resolver sozinho, mas como tem algo a ver com power query precisei da ajuda da Melissa. Achei que ela levaria pelo menos dois dias, mas ela conseguiu encontrar uma solução na hora.

A Melissa vai nos mostrar como resolveu esse complicado problema. Acho que a maioria das pessoas lida com esse tipo de problema e está procurando maneiras de resolvê-los.

Melissa: A primeira dica é se você estiver olhando para um arquivo de comprimento fixo, você pode ir para a aba View e ativar a opção Monospaced .

Podemos ver que é uma fonte de comprimento fixo. Também podemos ver os cabeçalhos, tabelas iniciais e subtabelas. Essas são as partes que nos interessam e das quais queremos extrair.

Além disso, certifique-se de ter sua barra de fórmulas ativada. É sempre bom tê-lo visível em sua tela, pois o usaremos com frequência para fazer pequenas modificações na entrada.

Criei um parâmetro para o local do arquivo onde armazenei o arquivo CSV. Eu trouxe isso como um arquivo de preparação e o subscrevi no meu parâmetro de localização de arquivo. Então eu criei uma referência e estarei trabalhando a partir dessa referência. Então é isso que estamos vendo agora na consulta de energia da Microsoft.

Índice

• Adicionando uma coluna de índice
• Criando uma lista de buffers no Microsoft Power Query
• Dividindo cabeçalhos das linhas
• Removendo os nulos e espaços em branco no Microsoft Power Query
• Dividindo as seções
• Cortando as strings de texto no Microsoft Power Query
• Conclusão

Adicionando uma coluna de índice

Normalmente, quando começo a trabalhar em um arquivo como este, preciso conhecer os requisitos do cliente. Pergunto o que o cliente precisa e o que procurar.

Nesse caso, queremos o número do item e o número do trabalho dos cabeçalhos e, em seguida, queremos todos os detalhes que pertencem a esse cabeçalho específico.

Vamos precisar de uma chave para juntar essas coisas. Mas se não houver nenhuma chave presente, então meu objetivo é adicionar uma coluna de índice . Clicarei no ícone da minimesa, selecione Adicionar coluna de índice e adicione De 0 .

Usarei uma lógica mais tarde com listas, que têm o índice baseado em 0. Ter seu índice começando do zero, na verdade, significa que você pode referenciar a mesma linha. Caso contrário, você terá que subtrair 1 para chegar a essa posição baseada em 0.

Em seguida, precisamos encontrar a posição onde estão nossos cabeçalhos, o que podemos fazer com bastante facilidade porque esses cabeçalhos são repetidos constantemente ao longo de todo o arquivo.

Para começar, vamos copiar este valor:

Adicione uma nova consulta em branco, cole-a e chame-a de HeaderID .

Farei o mesmo processo para as subtabelas. Vou copiar essa string de texto, criar outra consulta em branco e colar esse valor. Essa será a string que usaremos ao procurar linhas detalhadas.

Se esse processo de alguma forma alterar o cabeçalho de qualquer uma dessas tabelas, basta alterar uma das strings de texto e o arquivo funcionará novamente.

Eu realmente não preciso mergulhar no código M para procurar aquela string que estamos procurando. Podemos apenas usar isso como um parâmetro.

Vamos habilitar a carga para essas duas consultas.

Criando uma lista de buffers no Microsoft Power Query

A primeira coisa que farei é transformar a Coluna1 em uma lista referenciando-a e carregando-a na memória uma vez. Dessa forma, não preciso fazer chamadas repetidas para o arquivo.

Vou abrir o editor avançado e colocá-lo totalmente no topo. Quando você usa a interface do usuário para criar seu código, ele fará referência à etapa anterior.

Quando você coloca uma etapa de buffer em qualquer outro lugar em seu código e deseja fazer uma modificação, posteriormente, isso ajudará você a fazer as alterações na etapa que está criando manualmente.

Vou chamar isso de BufferList e referenciar Column1. Para carregá-lo na memória, adicionarei uma etapa List.Buffer .

Aqui está minha variável no topo. Posso fazer referência a isso várias vezes.

A primeira coisa que quero determinar é onde meus cabeçalhos começam porque preciso de uma chave para manter essas seções de cabeçalho e obter um único valor para todas essas linhas. Para fazer isso, adicionarei uma coluna personalizada e a chamarei de Header .

Vou escrever que, se Column1 for igual ao nosso ID de cabeçalho, quero que meu número de índice seja nulo.

Como resultado, encontrou o texto e retornou 5 e 23.

Preciso desse valor em todas as linhas, então preciso preenchê-lo. Você pode clicar com o botão direito do mouse para preencher, mas também pode usar uma sintaxe muito simples e adicioná-la à barra de fórmulas.

Neste caso, adicionei Table.FillDown e na string de texto, indiquei qual coluna queremos preencher (Header).

Agora, preenchemos isso para todas as linhas. Temos uma chave para todas as seções de cabeçalho e todas as seções de linha porque todas compartilham esse valor.

Dividindo cabeçalhos das linhas

A próxima etapa é separar os cabeçalhos das linhas. Vou adicionar outra coluna personalizada e chamá-la de Temp . Desta vez, faremos algo mais elaborado e aproveitaremos o BufferList que criei anteriormente.

Usaremos algumas funções de lista para examinar cada uma das posições e descobrir se há uma correspondência com o índice.

Começarei com uma instrução if e usarei List.Contains para procurar uma posição específica no BufferList e fazer referência à consulta HeaderID .

Queremos localizá-lo em todo o comprimento do arquivo e, em seguida, retornar a posição do item na lista. Se corresponder ao índice, teremos uma correspondência para essa linha específica.

Então eu quero retornar um valor para identificar o cabeçalho. Nesse caso, estou apenas retornando um H. Vou copiar a sintaxe para não ter que escrever tudo de novo.

Também precisamos identificar a seção de linha. Se a lista não contiver o HeaderID , mas o DetailID , estaremos em uma seção de linha.

Se Column1 for uma string de texto vazia, quero que ela permaneça vazia . Se não for esse o caso, quero que seja null .

Essa oposição obteve a linha de cabeçalho e retornou um H e, em seguida, encontrou uma linha detalhada e retornou um R. Em seguida, retornou 0s para todos os itens compartilhados nessa seção de linha.

Esses espaços em branco ou nulos são importantes porque permitem que você preencha. O preenchimento não se moverá pelas células em branco para que possamos eliminá-las mais tarde.

Faremos isso na barra de fórmulas e usaremos Table.FillDown novamente. Ele quer uma lista com o nome da coluna, que é nossa coluna Temp .

Agora temos os valores H e R repetidos por toda esta coluna, o que significa que podemos dividir os cabeçalhos das seções detalhadas.

Você também pode preenchê-lo na interface do usuário se não quiser escrever o código. Você pode simplesmente clicar com o botão direito do mouse e selecionar Preencher e, em seguida, Abaixo .

Removendo os nulos e espaços em branco no Microsoft Power Query

Agora que temos esse direito, podemos eliminar as coisas que não precisamos. Tudo o que é nulo ou contém um espaço em branco são as linhas que não precisamos e devem ser removidas. Podemos eliminá-los por filtragem.

Dividindo as seções

Depois de removermos esses espaços em branco e nulos, ficamos com tudo o que precisamos. Neste ponto, podemos apenas dividir as seções. Podemos nos concentrar nas linhas de cabeçalho e selecioná-las porque elas têm um espaçamento separado de todas as linhas de detalhes (que também têm um espaçamento separado).

Vou adicionar uma nova etapa na barra de fórmulas que me permite criar outro filtro nessa mesma coluna. Nesse caso, manterei apenas todas as seções de cabeçalho.

Agora, tenho todas essas linhas de cabeçalho aqui.

Posso selecionar Column1 , ir para a barra de fórmulas, selecionar Dividir coluna e dividir por posições.

Em seguida, faça com que a própria consulta de energia descubra isso. Ele irá sugerir algumas posições. Clique em OK para aceitar essas posições.

As únicas coisas que nos interessam nos cabeçalhos são o item e o número do trabalho .

Dentro da barra de fórmulas aqui, posso renomeá-los com Item e Job # . Isso me salvará de outra etapa de renomeação da coluna.

Após esta etapa, tudo o que preciso fazer é selecionar o Item , selecionar o Job # e, claro, selecionar nossa chave de cabeçalho . Em seguida, removerei todas as outras colunas porque não preciso mais delas.

Este será o resultado. Ainda precisamos limpar os valores e remover o item de texto e os traços. Tudo o que queremos são esses valores intermediários.

Então, abrimos e desmarcamos os traços e itens.

Agora, todos os cabeçalhos estão prontos.

Também temos que fazer o mesmo processo para DetailID . Vou precisar renomear essas etapas para que seja mais fácil voltar a elas um pouco mais tarde.

Voltaremos à consulta inicial que iniciamos. Começamos com Filtered Rows no painel Applied Steps.

Vou copiar isso e adicioná-lo ao meu filtro. Desta vez, não estou selecionando H, mas sim o R.

Em seguida, seleciono a Coluna 1, vou para a coluna Dividir, divido por posições e, em seguida, faço uma consulta avançada para descobrir.

Isso é o que a consulta de energia sugere. Vamos tentar.

Isso realmente parece muito bom. Mesmo as linhas totais se dividem perfeitamente. Claro, há muitos espaços porque tínhamos esse recuo.

Cortando as strings de texto no Microsoft Power Query

Selecionarei a primeira coluna e pressionarei para baixo + Shift para selecionar até a coluna 1.10. Vá para Transform , selecione Format e depois Trim . Aparar apenas removerá os espaços em excesso na frente ou no final da corda, não no meio.

Em seguida, podemos apenas promover os cabeçalhos, para que não seja necessário digitar todos os cabeçalhos ou títulos dessas colunas. Na etapa de divisão, renomeei duas colunas. Agora, é claro, com 10 colunas, isso é um pouco incômodo.

Também temos que nos livrar desses valores em excesso. Como temos totais, tenho que usar uma dessas três últimas colunas porque são as únicas linhas que têm os valores adicionais em algum lugar intermediário. Em seguida, desmarcaremos esses espaços em branco, traços e textos.

Em seguida, removerei as colunas desnecessárias para que tudo o que resta seja uma tabela com apenas os cabeçalhos e apenas os detalhes. Precisamos de uma chave para reunir essas seções novamente.

Para isso, podemos usar uma auto-fusão para que possamos mesclar a tabela com ela mesma para reunir essas informações. Na guia Início , selecione Mesclar e, em seguida, selecione a Coluna 5 e a mesma consulta.

Em vez de AllDetails, quero AllHeaders como minha tabela inicial com a qual desejo mesclar.

Isso trouxe de volta todas as informações da tabela de cabeçalho com uma única linha para cada item e cada número de trabalho.

Usamos uma chave para mesclar com as linhas de detalhes. Se eu pressionar para o lado no espaço em branco aqui, veremos uma visualização de todas as linhas que pertencem ao Cabeçalho 5.

Vamos remover a coluna final aqui e, em seguida, terminamos de corrigir a largura da coluna fixa mista na consulta de energia da Microsoft.

Conclusão

Neste tutorial, criamos uma maneira de resolver problemas mistos de largura de coluna fixa usando o Microsoft Power Query. Se você gostou do conteúdo abordado neste tutorial específico, não se esqueça de se inscrever no canal de TV LuckyTemplates.

Temos uma grande quantidade de conteúdo sendo publicado o tempo todo, meu e de vários criadores de conteúdo, todos dedicados a melhorar a maneira como você usa o LuckyTemplates e a Power Platform.

melissa