Aprenda consulta de energia para Excel e tempo de SALVAR

1. Introdução: Power Query é o recurso mais incrível a ser adicionado ao excel em algum tempo. Neste curso Power Query Power, vou ensiná-lo a usar o power Query para se conectar a qualquer tipo de conjunto de dados, trazer os dados e como limpá-los usando uma técnica de automação passo a passo. Você tem cinco aulas em vídeo neste curso. Então este é realmente um mini-curso e você pode assistir isso em uma tarde. No final deste curso, você terá alguns poderes especiais dentro de você. Você poderá pegar qualquer tipo de dados que pareçam sujos ou mutilados ou todos mal formados. E então você pode dizer: Sim, eu posso limpá-lo e posso analisar meus dados com automações. Então, muito obrigado por fazer este curso. Vamos entrar em nossas lições agora. 2. Introdução a e 5 tarefas de limpeza de dados com Power Query: Aqui estão os dados de amostra que usaremos para os dois primeiros vídeos. Na verdade, usaremos os mesmos dados para todos os vídeos, mas apresentarei alguns conjuntos de dados adicionais. Para vídeos 34. Este é um conjunto de dados típico de funcionários porque é bastante neutro. Todos podemos nos relacionar com isso. Como você pode ver, embora pareça limpo, há alguns problemas com esses dados. Para acessar esses dados no Power Query, basta selecionar todos os dados. E, na faixa de dados, clique na opção Intervalo de tabela. Power Query insistiria que seus dados deveriam estar em um formato de tabela, portanto, ele permite que você crie a tabela. Neste ponto. Nós apenas dizemos, ok, em nossos dados são transformados em uma tabela. Isso abrirá o Power Query Editor. E esta é a tela através da qual podemos dizer ao Excel como queremos que nossos dados sejam limpos usando regras de processamento de dados. Podemos ver o nome da tabela aqui. É chamado de Tabela um porque criamos a tabela no nome do Excel do voo como TB1. Ele mostrará uma prévia dos dados. Pense no editor de consulta de energia como uma tela separada onde podemos ir e configurar várias coisas. Usaremos isso para responder às primeiras cinco perguntas de limpeza de dados e, em seguida, você se familiarizará com a tela nesse processo. Nossa primeira pergunta é que temos o ID de funcionário aqui, mas está tendo dois valores diferentes misturados. As duas primeiras letras de ID do funcionário ou meu código de centro de custo. E então as próximas cinco letras são o número do funcionário em si. Portanto, queremos dividir o centro de custo e o número do funcionário em duas colunas separadas para fins de análise. Para dividir a coluna, você pode selecionar a coluna e, em seguida, você pode clicar com o botão direito do mouse na coluna aqui, você terá uma opção Dividir coluna. Observe que existem diferentes maneiras de dividir a coluna. Por exemplo, podemos dizer que eu queria dividir por número de caracteres. Isso é realmente o que precisamos. Quero dividir dois personagens e cinco personagens. Alternativamente, se você tiver um delimitador, você pode usar essa ou algumas outras opções interessantes aqui também. Seria usado o número de caracteres e especificaria que eu quero dividir dois caracteres uma vez o mais à esquerda possível. Isso só vai dividir a coluna em duas colunas separadas, duas primeiras letras e tudo o mais. Quando você clica em OK, isso criará duas colunas de ID de funcionário separadas. Portanto, sua coluna de ID de funcionário original agora desapareceu. E, em vez disso, o Power Query fornecerá duas colunas separadas para trabalhar. E eles receberão o nome de ID um e ID dois. Você pode clicar duas vezes na coluna e nomear isso como centro de custo. E este seria o número de identificação. Aqui. Através desse processo, o que fizemos foi ter o Power Query careca que, com esses dados de ID de funcionário, dividi-os em duas colunas. Portanto, enquanto isso acontece com o conjunto de dados atual, sempre que você tiver novos dados, se você executar o processo de atualização, o Power Query irá capturar os novos dados. Ele irá dividir automaticamente que os dados também são problemas mistos é, na coluna do departamento podemos ver que várias pessoas têm departamento nulo. Essas pessoas nem deveriam estar em nossa lista de pessoas. Eles não estão mais trabalhando aqui, mas de alguma forma nosso sistema de importação de dados meio que os traz. Então, sabíamos que queríamos remover essas pessoas. Todas as linhas dos dados. Você pode usar a opção de filtro. Então clique no filtro e se desmarcarmos qualquer coisa que não faça parte dos dados finais que o Power Query fornecerá. Então, se eu estiver desmarcando o nulo, não vejo mais isso. Posso usar a mesma tela. Por exemplo, exclua qualquer equipe de treinamento e equipe de suporte se eu não quiser vê-los para uma análise específica ou situação de dados. Nesse caso, manteremos todos esses, mas apenas excluímos os funcionários nulos. E quando você clica em OK, as pessoas que também passaram dos dados para ver as etapas que aplicamos nos dados, você pode ver a área Etapas aplicadas aqui neste canto. E então ele listará todas as etapas que você tomou até agora, limpe os dados. As etapas que fizemos são classificamos os dados do Excel. Portanto, esta é a etapa de origem. Em seguida, dividimos a coluna por posição. Portanto, essa coluna é dividida em duas colunas, ID de funcionário um e funcionário 82. Quando você clica em uma etapa específica na consulta, ela mostrará os dados a partir da etapa holandesa. Nesta etapa, quando dividimos as colunas ainda são chamadas de ID um e ID dois. Então, na terceira etapa, renomeamos as colunas. E então, na quarta etapa, aplicamos um filtro na coluna do departamento para que removamos o departamento nulo. Digamos que mudei de ideia. Recebo o correio da equipe financeira que nosso código de centro de custo não é duas primeiras letras, mas são as três primeiras letras. Como mudamos isso? Basta olhar para o passo em que você fez a mudança. Por exemplo, Dividir coluna por posição é o passo. Localizamos isso. E depois há um botão de ganho ou engrenagem ao lado do passo. Você clica nisso. E aqui você pode apenas dizer que primeiras três letras são o meu código de centro de custos. Então, faça com que os dois S3 cliquem em OK. E você sabe o quão rápido três letras aqui, e então o resto delas está lá. Tecnicamente, isso não está correto. Então, vou desfazer essa etapa alterando-a de volta para dois, independentemente de qual etapa você está visualizando. Sempre que você terminar seu trabalho no Power Query, ele só fornecerá os dados a partir das últimas etapas. Portanto, não lhe dará os valores intermediários, apenas o último passo sozinho. terceiro problema de limpeza é que se você notar a coluna do nome, você pode ver que alguns nomes têm algum espaço extra no final ou no início. Então aqui tenho meus espaços extras. Isso é novamente, um problema comum em muitos cenários de limpeza de dados quando você traz os dados de um sistema externo, às vezes ele terá alguns espaços extras. Assim, podemos limpar esses espaços. Para limpar o Espaço, clique com o botão direito do mouse na coluna de nome e ouça do formulário de ramificação. Você tem a opção de cortar os dados. O que faremos é remover quaisquer espaços extras no início ou no final. Então, corremos a guarnição e esses espaços extras desapareceram. Até agora, fizemos três etapas de limpeza de dados. Nós dividimos o ID do funcionário, tiramos os espaços extras e retiramos todos os funcionários do Departamento. Para nosso cenário de limpeza de dados mistos, temos a data de início dos funcionários e só queríamos calcular quanto tempo eles estão aqui com a organização. Como a vênula. Temos uma coluna de data aqui, mas observe que o tipo de dados aqui diz ABC123. Antes de fazermos qualquer operação nas datas, é uma boa ideia converter os dados no tipo de dados de dados. Para fazer isso, clique com o botão direito do mouse na Data de início Alterar Tipo e selecione a opção de data. Quando você tiver o tipo de dados de data automaticamente, o ícone aqui mostrará um logotipo do calendário em vez de ABC123, que era o que estava mostrando anteriormente. Agora que a data de início é tratada como uma data, posso ir e introduzir uma nova coluna que analisa a data de início e, em seguida, minha data atual. A data da gravação deste vídeo é 31 de março de 2022. E então me diga quanto tempo cada um desses funcionários está conosco. Para fazer esse tipo de operação em casa, você precisa ir para a faixa Adicionar coluna. E a partir daqui posso introduzir um cálculo como uma nova coluna. A partir daqui na data. Vou selecionar a primeira opção, Adicionar data da coluna, idade. O que isso fará é calcular a idade da data dos dados partir do dia atual menos essa data é qual seria a idade. Quando você adicionar idade, você receberá o Aij adicionado aqui. A idade será mostrada no formato de horas base, minutos e segundos. Então, diz que esse funcionário está conosco há 503 dias. Essa pessoa precisa aqui por 1344 dias. Embora essa seja uma boa maneira calcular a posse dos funcionários, ou 503 não é realmente significativo. Idealmente, queremos calcular quanto tempo eles estão conosco nos ouvidos ou algo branco. Mantendo a coluna de idade selecionada. Podemos ir para a fita Transform e, a partir daí, posso mudar a idade para outro formato. Então, adicionar coluna adicionará a coluna, enquanto a transformação mudará as coisas no lugar. A partir de páginas de transformação mostradas em um formato de duração. Você pode ver o relógio parado, tipo de ícone lá na duração do quadro. Posso dizer com certeza, essa idade para mim em ouvidos totais. Isso calculará o mandato do empregado no EX que esta pessoa está aqui há 1.307 anos e essa pessoa está aqui há 3,68 anos neste momento, você também pode clicar duas vezes na idade e em seguida, renomeie isso como posse. Esse cálculo de idade é um cálculo dinâmico. Portanto, toda vez que você atualizar o Power Query, ele lerá esse cálculo e atualizará o mandato a partir da data de atualização do duto. Neste momento, é 31 de março de 2022. Mas no futuro, sempre que você estiver assistindo a este vídeo e estiver atualizando esses dados, você verá um valor diferente aqui. Uma coisa que você pode notar se estiver seguindo comigo, facilidade, sua Power Query está limpa, provavelmente está um pouco diferente e os passos também estão parecendo um pouco diferentes. Então, há algumas coisas que eu fiz no meu fim. E vamos revelar que a diferença número um que você pode notar é que você pode não ter a barra de fórmulas na parte superior. Isso é algo que é opcional e eu gosto de manter isso. Para ver a barra de fórmulas, você pode ir de transformado para o botão de beleza e ativar a opção de barra de fórmulas. Isso é uma coisa única. E quando você fizer isso, toda vez que você abrir Power Query de qualquer outro arquivo do Excel, ele mostrará a barra de fórmulas. Você pode pensar qual é o ponto da barra de fórmulas O Power Query usa sua própria linguagem chamada linguagem M. E como a linguagem M é bem diferente da linguagem de fórmulas do Excel, gosto de entender o que está acontecendo de tempos em tempos. Se tivermos a barra de fórmulas lá, você pode ler o que está acontecendo e dar sentido às etapas, porque cada etapa tem um pedaço de lógica ou código anexado a ela. Por exemplo, a etapa Colunas renomeada aqui diz que Você pegou a idade da coluna e , em seguida, renomeou para posse. Então essa é a lógica que a parte 20 está usando. Da mesma forma para calcular o total de orelhas. Se eu selecionar essa etapa, você pode ver que a duração levou o total de dias e, em seguida, dividi-la com 365 para calcular a idade. Aqui você pode ver que isso não está prestando atenção aos anos bissextos ou qualquer coisa independentemente do que seja o ouvido, está sempre se dividindo com 365. Ter essa barra de fórmulas é uma ótima maneira de dar uma olhada no que está acontecendo nos bastidores. E eu gosto de manter este para meus próprios propósitos de diversão. Vamos concluir esta lição adicionando mais um cálculo, que é dado o FDG de um funcionário. Eu queria adicionar uma coluna que me dissesse se eles são funcionários em tempo integral ou funcionário a tempo parcial. Esse tipo de cálculo é chamado de cálculo condicional porque se eles são IED é um, então eles são em tempo integral. Qualquer coisa menor que um é meio período. Podemos usar a opção Adicionar coluna condicional de coluna para introduzir esse cálculo. Então adicione coluna Coluna condicional. A partir daqui, especifique o nome da coluna. Então esta é a minha parte de barra completa, esse é o nome da coluna. Em seguida, construímos a condição usando essa tela específica. Então, se a coluna FDE, e então aqui ela diz igual a menos que, maior que etcetera. Então, vou dizer que ys menos de um. Isso significa que eles são o valor equivalente em tempo integral está abaixo de um, então eles são funcionários a tempo parcial. Você pode construir uma escada de colisões aqui por enquanto, vamos apenas fazer if-else. Se eles estão abaixo de um lá em meio período, senão eles são em tempo integral. E, em seguida, clicaremos em OK, e ele introduzirá um cálculo de parte de barra completa aqui como uma coluna para mim, o que marcar os funcionários como meio período ou período integral. Esse tipo de coisa é muito útil se eu quisesse fazer alguma análise sobre a natureza dos funcionários a jusante. Agora que temos cinco etapas de limpeza que estão dividindo o centro de custos dos funcionários e o número de identificação, limpando o nome, removendo o departamento, adicionando seu mandato e adicionando sua natureza de trabalho, seja em tempo integral ou meio período. Vamos carregar isso de volta ao Excel para que possamos ver como os dados carregados que realmente se parecem antes de fazermos isso é chamado de tabela um. A tabela um não é um nome muito bom, então vou nomear minha consulta como. Você pode. Basta selecionar a opção de nome aqui e, em seguida, digitar o nome. Vou chamar isso como equipe e clicar em Enter. Então, agora a consulta será chamada de equipe de casa, que tem um grande botão Fechar e Carregar. Por enquanto, clicaremos nisso nos vídeos subsequentes, direi como usar as outras opções de carregamento também. Então, diremos Fechar e carregar. Então o que isso vai fazer é carregar uma tabela de cores verde. Eu acho que esta é a cor padrão que estaciona onde ele escolhe o tempo todo no Excel em uma nova guia e o nome que ela tem, a guia é chamada de equipe, bem como a própria tabela que ela cria é também chamado de equipe. Esta tabela verde aqui é a versão mais limpa de seus dados. Seus dados originais ainda estão aqui na guia Dados. Observe que quando você criou a Power Query, ela meio que a transforma em um formato tabular. Então, esses são meus dados originais e esses são meus novos dados. Você pode ver isso aqui, Darren Scalia, o ID do funcionário, SBI zeros zeros 07. Na versão mais limpa, ele foi dividido em PR e zeros 07. Da mesma forma, o qualia de Darwin é o que, 0,3 FTE, e eles se juntaram em 13 de novembro de 2020. Então, com base nessas informações, esses dois calculamos seu mandato e, em seguida, os identificamos como funcionários de meio período também porque eles têm um departamento listado aqui. Mas se eu for aqui e depois olhar para algumas dessas pessoas como um minuto enquanto Stafford, David e não estar aqui nesses dados porque eles pertencem ao departamento nulo. Então, quando os dados foram limpos, eles meio que saíram dos dados. E você pode ver neste filtro aqui que não temos o departamento nulo NADH. Também teremos Minerva nos resultados da pesquisa aqui. É assim que os dados são limpos se algo mudar. Então, por exemplo, recebemos a mensagem de segundo plano corporativo que Minerva não é uma funcionária nula do departamento, ela se mudou para o legal. Eu mudei meus dados. Da mesma forma, o adiantamento atualizará seu IDE de 0,3 para 0,6. Na verdade, vou fazer deles um que veremos que eles se tornam em tempo integral. Fizemos duas alterações. Fizemos os torrents como empregados em tempo integral e cortamos Minerva para o departamento jurídico. Viemos aqui para buscar os dados do personagem. Novamente, você só precisa clicar com o botão direito do mouse e atualizar os dados. Você pode fazer isso de várias maneiras. Você pode clicar com o botão direito e atualizar. Você pode ir para Dados e, em seguida, atualizar. Ou se você tiver a consulta algum painel de conexão mostrado no lado direito, você também pode clicar com o botão direito do mouse aqui e depois atualizar. Independentemente de como você faça, você está apenas fazendo o mesmo processo. Então, vamos atualizar isso. E boom, temos Minerva aqui. Temos tolerância aqui com o DEF d1 e, em seguida, eles se tornam funcionários em tempo integral. Não há mudança em seu mandato porque ainda estamos reexecutando o processo em 31 de março. Mas se eu quiser ler sobre isso novamente amanhã ou dia depois, vou ver valores completamente diferentes para isso também. Então esse é o primeiro vídeo que carregamos os dados no Power Query. Fizemos cinco tarefas de limpeza e depois as carregamos de volta para o Excel. Vamos passar para o próximo. 3. Adicionando colunas em Power Query: Na lição anterior, pegamos os dados da nossa equipe e, em seguida aplicamos cinco etapas de limpeza de dados, que estão dividindo o ID do funcionário em centro de custo e número de ID válido, removendo espaços da coluna de nome, e, em seguida, retirar qualquer pessoa que esteja no departamento nulo, bem como com base na data de início, calcule seu mandato e, com base no FTE, descubra se há funcionário a tempo ou meio período. Esses são os dados que geramos após a limpeza usando o Power Query. Nesta lição, vamos continuar o processo de limpeza e adicionar mais algumas etapas a ele. Agora que estamos aqui no Excel, como voltamos à consulta de parte? Você pode fazer isso em algumas etapas diferentes. Número um, você pode ir de casa para a faixa de dados. E a partir daqui, você tem suas consultas e conexões. Se você clicar nessas consultas e conexões, verá todas as consultas em sua pasta de trabalho. Portanto, esta é minha consulta de equipe agora, só temos uma, mas potencialmente você pode ter várias consultas também. Quando você olha para uma consulta específica, você pode clicar com o botão direito do mouse e dizer Editar declaração. Da mesma forma, a segunda opção é que você pode clicar na tabela, a tabela verde gerada pelo Power Query. Sempre que você clicar dentro desse tipo de tabela de consulta de energia aérea, você verá que há uma faixa de consulta aparece na parte superior. E se você for lá, você tem o bom botão Editar na palavra-código também. Você pode usar isso para voltar à nossa consulta. Vamos editar isso e começar a fazer um pouco mais de limpeza dos dados. Como é que a etapa de limpeza rápida neste vídeo estará na coluna salarial. O que é essa coluna salarial tem alguns valores nulos. E qualquer um que tenha obtido salário nulo, isso é por causa da política de RH específica. E assim, qualquer um que está recebendo exatamente $45 mil salário aparece como um nulo. Não me pergunte, apenas cenário inventado aqui. Queríamos apenas substituir as palavras por 45 mil, então esse deveria ser o membro deles. Mas antes de fazermos isso, vamos entender rapidamente o que essas barras verdes no topo significam. Essas barras verdes nos dizem a qualidade dos dados da coluna. Se alguma coisa estiver totalmente verde, isso significa que ele tem tipo de dados todos ou a coluna. Mas se algumas colunas tiverem alguns valores nulos, como aqui, eu tenho valores nulos. A barra verde não está totalmente cheia. E quando você passa o mouse, ele me diz que 931 linhas têm valores e 4141% mais 4% estão vazios. E também me dá a opção de remover o vazio. Não queremos remover nada vazio. Só queremos lidar com o problema salarial aqui. Então, aqui, dentro do salário, por exemplo, 40 valores estão vazios. E também destacaria se houver algum erro no momento, não temos nenhum erro. Mas se houver erro, ele também destacará isso. Vamos apenas adicionar uma regra que diz que se o salário for nulo, então o valor deve ser 45 mil. Esse tipo de processo é chamado de substituição. Portanto, tudo o que você precisa fazer é clicar com o botão direito do mouse na coluna e usar a opção Substituir valores. Você pode estar pensando, por que não posso simplesmente selecionar isso e digitar ou os dados? Você não tem permissão para fazer esse tipo de coisa dentro do Power Query. Power Query é um mecanismo baseado em regras, portanto, cada etapa de limpeza que você fizer deve ser uma ruína. Então, vou clicar com o botão direito do mouse e depois escolher Substituir valores. O valor para encontrar 0s e nulo. Queríamos substituir o Ártico 45.001. Atenção aqui, Power Query diferencia maiúsculas de minúsculas Então, se você digitar null exatamente do jeito que ele aparece, então apenas para funcionar. Se eu fosse digitar isso em letras maiúsculas, mesmo que para nossos olhos eles sejam os mesmos. Esse é um valor diferente e isso não vai funcionar. Então vamos dizer null 45 mil e depois clicar em Ok. Em seguida, adicionará uma regra que diz que se o salário de alguém for conhecido, então será 45 mil. Agora, nossa coluna salarial é verde por todo o caminho. Nosso próximo cenário de limpeza recebe o nome do funcionário, quero extrair apenas o primeiro nome e depois imprimi-lo em uma coluna diferente. Poderíamos então usar isso para talvez enviar uma carta ou o que quer que seja. Então você só quer imprimir o nome do próprio nome. Esse tipo de coisa é onde eu quero extrair tudo até o primeiro espaço. Então esse é o nome do host. Você poderia fazer isso de algumas maneiras diferentes. número um é que você pode selecionar a coluna e , a partir da coluna, você pode usar a coluna a partir dos exemplos. Dessa forma, você permitirá que o Power Query descobrisse qual é a lógica para fazer essa operação. Pense na coluna de exemplos como o Flash Fill no Excel. Mas aqui com uma coluna de exemplos, é repetível. Isso significa que, se seus dados mudarem e você ler e o processo, ele também é chamado extrair os primeiros nomes para os novos dados. Faremos essa coluna a partir de exemplos. E daqui vou digitar meu primeiro nome, Torrance. E então, neste momento, você meio que previu o que estamos buscando. E então, é em breve todos os outros valores que ele obterá se o primeiro valor for ensinado. E vamos apenas ler a fórmula também. Qual fórmula está usando, é dizer x-dot antes do delimitador. Olhando para a coluna de nome e o delimitador é espaço. Essa é a fórmula real da linguagem M que estaciona onde ele está usando aqui. Quando você está feliz com isso, você clica em OK. E então isso é adicionado se você for chamado de texto antes do delimitador, vou clicar duas vezes sobre isso e depois dizer FirstName. Esse é o nome dessa coluna. E obteremos o primeiro nome do funcionário aqui. Ele ainda manterá seu nome original, mas adicionará uma nova coluna chamada firstname. Agora que adicionamos isso, quero ver esse primeiro nome ao lado do FullName, não até aqui. Uma maneira de mover esta coluna é clicar na coluna, segurar o mouse e depois movê-lo ao lado da coluna de nome. Isso reorganizará as colunas com o nome aqui mais um aqui. Você pode olhar para o código de idioma M aqui que ele diz tabled ou para reordenar colunas. Continuaremos nossa discussão com a coluna de nome novamente. Dado o nome, quero reescrever o nome como, por exemplo, taurina Scalia é o nome deles. Eu quero chamá-los como qualia, tolerância à câmera, forma de vírgula chancy, vírgula de pássaro. Novamente, é assim que, em determinadas situações, você quer ter sobrenome, vírgula, primeiro nome como opção. Faremos outra coluna, que será nome2. E então é aí que o indutor e se evitar serão feitos. Novamente, poderíamos usar a coluna a partir de exemplos opção. Mas porque você já fez isso, vou mostrar uma técnica diferente, essa técnica diferente em paredes daquela coluna temos uma opção de extração. A partir da extração eu corrigi antes do texto do delimitador após as opções do limitador. Vou usar isso para mapear o nome para duas colunas diferentes. Já temos a coluna do primeiro nome, então usaremos o primeiro nome, apenas geraremos o sobrenome e, em seguida, faremos isso. Então, extraia o texto após o delimitador. Então o delimitador é espaço. Então, vou apenas pressionar espaço aqui e clicar em Ok. Isso tem que extrair endoderme. Coloque-o todo o caminho no final. É tudo texto após delimitador, e então só terá esses sobrenomes impressos aqui. Observe que alguns desses sobrenomes têm algum espaço extra. E a grande coisa também, isso ocorre porque o nome do fornecedor é inserido no sistema. Ele faz como vários espaços no meio e esses espaços não são removidos por guarnição. É por isso que nesses espaços estão chegando lá em cima. Isso tudo é bom. A grande coisa para delimitar está chegando. Posso clicar com o botão direito do mouse rapidamente, aparar isso também. Então esse negócio em excesso se foi. Agora temos um sobrenome aqui. Em um primeiro nome aqui. Então o que queremos fazer é pegar o texto após o delimitador, colocar uma vírgula e depois fazer o vapor na coluna final. Aqui usaremos a opção de coluna personalizada. Temos a coluna condicional, aplicamos a coluna a partir de exemplos. Vamos tentar a coluna personalizada. É aqui que ele abrirá uma tela pedindo que você escreva o idioma M você mesmo. Esta é minha coluna nome2. Aqui. Escolherá o texto após o delimitador. Esse é o sobrenome, e comercial, dentro do espaço de vírgula de aspas duplas. E depois escolheremos o primeiro nome. Esta é a fórmula da linguagem M para introduzir um novo nome que leva duas colunas e, em seguida, coloque o espaço de vírgula no meio ali. Clique Ok, isso foi chamar seus pais chancy, vergonha, mas gati assim. Agora que esta coluna é gerada, não precisamos mais desse cara aqui. Nós só o usamos para chegar lá. Neste ponto, você pode clicar com o botão direito do mouse no texto após o delimitador e, em seguida, remover esse botão. Ou, se você quiser mantê-lo, você pode mantê-lo e renomear isso como sobrenome. Só vou removê-lo. Agora temos um nome2. E novamente vamos movê-lo ao lado do nome. Temos nome, name2 e, em seguida, FirstName aqui, tudo bem listado. última transformação ou nossa última limpeza de dados neste vídeo em particular estará analisando a data de início. Sabemos há quanto tempo um funcionário está conosco, mas alguns dos funcionários também nos deixaram. Por exemplo, temos uma data de rescisão nos dizendo que, a partir de 24 de setembro de 2021, esse funcionário em particular, Ali, deixou a organização com base na data de rescisão. Eu queria saber se um funcionário é o funcionário atual ou não. Teremos uma coluna indicadora ativa. Primeiro passo, você pode ver que a data de rescisão é ABC123. Então, vou clicar com o botão direito do mouse em Alterar Tipo até o momento. Dessa forma, você pode ter uma data ou uma calmaria. E, novamente, a qualidade da coluna me diz que apenas 80 pessoas têm uma data de rescisão. 892 estão vazios. Isso significa que eles ainda são funcionários atuais. Com base nisso, quero adicionar uma coluna que me diga se o ponto atual deles não tem nada. Esse tipo de coisa é perfeito para a coluna condicional. Então, vamos usar adicionar coluna, coluna condicional e, em seguida, ver se a data de rescisão é igual. E então aqui você pode simplesmente digitar nulo e a saída é sim, isso significa que eles são funcionários ativos. Não, não significa que eles não agem mais para clicar em Ok. Qualquer um que riu e eles não terão lá porque eles terão uma data de dominação. Isso conclui este vídeo em particular. Mas antes de você desaparecer, tenho uma tarefa de lição de casa para você. A tarefa de casa ys na coluna salarial. Eu quero que você olhe para a coluna salarial e, em seguida, crie um grupo salarial tem uma nova coluna. A lógica do grupo salarial é simples. Se você absorver a coluna salarial, nossos salários vão de US $28 mil até US $119 mil. Portanto, pode ser US$120 mil. Queremos agrupar nossos funcionários em quatro baldes. Qualquer pessoa com menos de 50 mil, qualquer um com menos de 80 mil, qualquer um com menos de 100 mil, e depois mais de 100 mil. Então, esses são os quatro grupos com menos 5050 a 808100 e mais de 100. Com base no salário, você precisa introduzir uma coluna extra aqui chamada grupo salarial, onde esse valor pode ser mantido. Deixo para sua imaginação sobre como fazer isso. Mas se você tiver algum problema com isso, assista ao vídeo da solução de lição de casa que explica esse processo. Por enquanto, vou fechar este em particular clicando em roupas. E Senhor, isso só vai atualizar nossa mesa verde. Esses cálculos extras e colunas de nome extra agora para que você possa ver que o coxo original está aqui, nome2 mais nome e todas essas outras coisas também. Então, se eu quiser atualizar a data de rescisão de alguém nos meus dados originais. Por exemplo, os pais decidiram sair em 31 de março de 2022. Então, colocamos a data de rescisão deles aqui. Você vem aqui, clique com o botão direito do mouse e atualiza. Imediatamente. Eles comprarão mercado, pois não mais ativos e a data de morte da rescisão aparecerá aqui também. Isso é boa sorte com sua lição de casa. Vou te pegar no próximo vídeo. 4. Solução de casa: Para adicionar o agrupamento salarial como uma coluna, você pode usar a opção de coluna condicional e construir esse tipo de escada. Então, usaremos essa coluna condicional. Aqui. O nome da coluna é grupo salarial. Se meu salário for inferior a 50 mil, então a saída seria inferior a 50 K. Então adicionaremos mais uma cláusula. Se o salário for inferior a 80 mil, a saída seria 5280. Eu ensinei a cláusula, que é se o salário for inferior a 100 mil, então ele precisa ser 8200 K. Caso contrário, não precisamos fazer isso para mais uma cláusula aqui, podemos apenas usar a cláusula else. E então diremos que mais de 100 K. Então essas são as saídas. Você pode digitar literalmente qualquer coisa aqui. Você poderia, por exemplo, ter um rótulo como salário baixo, médio, alto, muito alto ou o que quer que seja. Quando você terminar de fazer isso, se você clicar em OK, é chamado adicionar esses agrupamentos salariais aqui para você. Esta é uma excelente maneira de trazer algum tipo de valores extras com base em regras de negócios e usá-los no Excel. Você poderia ter construído esse tipo de coluna no Excel também, mas como este é um vídeo de consulta de porta, estou ensinando como fazer isso dentro do Power Query. Se você alterar as condições e digamos que nossos critérios não são mais 5080, mas é 5075. Então você pode usar o botão de engrenagem aqui. O botão de engrenagem, posso clicar nele. E isso vai ter esses limites aqui, e eu posso simplesmente mudar o valor. Então, aqui a partir dos 80, vou apenas parecer isso para 75 mil. E os valores renomeados aqui como 7575 a 100 K assim. Certifique-se de adicionar apenas todas as coisas que precisam ser ajustadas sempre que esses tipos de coisas mudarem e quando você clicar em OK, isso será atualizado. Outra maneira de fazer esses tipos de alterações, IZ em vez de usar o botão de engrenagem, se você tiver a barra de fórmulas visível na tela, você também pode editar diretamente os itens na barra de fórmulas. Talvez sejam menores, o alcance mais baixo não é 5845. Então eu posso digitar 45 mil aqui e depois renomear meus rótulos, bem como 45 K. E isso é chamado de ajuste. Atualize também. Sempre que você terminar de digitar a fórmula, se você apresentador, isso também mudará essas coisas. 5. Mesclagem de tabelas e dados de filtragem no Power Query: Até agora, limpamos os dados em um conjunto de dados. Nesta lição, vou apresentar outro conjunto de dados e mostrar mais algumas técnicas para o propósito desta lição, vou supor que cada um de nossos funcionários vai receba um bônus com base no salário deles. A porcentagem de bônus é decidida com base no departamento. Então, se você estiver no departamento de treinamento, digamos que você receba 2% de bônus, mas no departamento de recursos humanos você pode receber 3% de bônus. Para que tudo isso aconteça, temos que seguir uma política específica. As políticas que o bônus só é concedido a funcionários que estão ativos no momento e que não são temporários. Então eles precisam ser permanentes ou fixos, vem a equipe que está ativa. Vamos entrar no Power Query e fazer esse processo onde pegamos qualquer um que seja um funcionário temporário ou inativo e, em seguida, trazemos os detalhes do bolus também. Se você for para o Editor de consultas, esta consulta aqui, Vamos em frente e remover qualquer pessoa que não esteja ativa. Então vou selecionar isso e depois dizer, não quero nenhum funcionário inativo. Da mesma forma, chegaremos ao tipo de funcionário e desmarcaremos o temporário. Neste ponto, reduzimos nossos funcionários para ajustar a equipe de sintomas dominantes e fixos que estão ativos. Agora precisamos calcular os jogadores com base em seu salário. Para isso, temos uma tabela de mapeamento em bolus. Vamos, vamos rapidamente Senhor isso de volta para que eu possa mostrar a tabela de mapeamento em bolus. Aqui está minha menor tabela de mapeamento. Ele me diz por cada departamento qual é a porcentagem de bônus que vamos ir? Precisamos trazer esses dados para o conjunto de dados que já temos e depois combiná-los para gerar os cálculos de bônus. Você pode, por exemplo, Controlar C isso vá aqui e depois colar esses dados. Mas como os dados estão em outro arquivo, também podemos nos conectar diretamente a isso. Em vez de copiar a colagem, usaremos a consulta do Spark para primeiro trazer os dados e depois combiná-los com esses dados. Vá para dados e, em seguida, obtenha dados do arquivo da pasta de trabalho do Excel. Isso ocorre porque minha tabela de mapeamento de bônus é um arquivo do Excel. Se você nasceu como mapeamento usando um arquivo de texto ou se não for um banco de dados, você pode usar essas opções. Então, vou usar o From Workbook. E então aponte para o meu mapeamento de bônus. Tenho uma versão mais avançada do problema de mapeamento em bolus no mapeamento em bolus para arquivos, sobre o qual falaremos no próximo vídeo. Isso mostrará uma tela do navegador pedindo que você escolha quais dados deseja. Quero dobrar minhas tabelas de mapeamento em bolus. Vou selecionar isso. Não precisamos desta tabela, em vez disso, precisamos calcular os bônus. Então, entraremos em dados transformados e isso carregará a tabela de bônus no meu parque já. Você pode ver que nossa mesa de equipe também está lá. Agora, minha tabela de bônus me diz qual é a facilidade de bolus para cada departamento da equipe que eles gostariam e ver a que departamento a pessoa pertence. Se eu conseguir chegar ao lado do departamento, eles porcentagem de bônus que eu tomei a porcentagem multiplicam isso com salário para obter o valor do bolus também. Então esse tipo de operação é chamado de margem para tabelas, onde eu quero combinar esta tabela na coluna do departamento com os enlutados Stevie. Aqui. Para fazer isso, você pode usar a opção Mesclar consultas da faixa inicial. Isso abrirá um diálogo. Pergunte a você qual coluna você deseja combinar. Então, na mesa da equipe, quero gerenciar um departamento com minha tabela de bônus. Departamento. Isso fornecerá rapidamente um feedback aqui, dizendo que você pode combinar todas as linhas aqui com elas. E, em seguida, basta clicar em OK. Aqui teremos a tabela de bônus associada para cada funcionário aqui. Se eu clicar nesta tabela, posso ver que, como essa pessoa é recursos humanos, eles são necessários para que o bolus seja 0 para essa pessoa gerenciamento de produtos, 0,05 ou 5%. Não precisamos da tabela, precisamos apenas extrair o valor do bônus. Para extrair o valor, você precisa clicar neste botão de setas laterais e desmarque o departamento manter o bônus e desmarque esta opção também use o nome da coluna original como prefixo. Dessa forma, só receberemos uma coluna que diz bônus. Boom, recebemos o valor do bônus. O valor do bônus aparece aqui como um número decimal. Vou clicar com o botão direito do mouse aqui, alterar o tipo para porcentagem. Isso me mostrará quais são as porcentagens de bônus. Isso só tem parte do problema. Nós recebemos a porcentagem de bônus, mas o que precisamos é realmente o valor desossado. Para obter o valor do bônus, precisamos receber o salário. Em seguida, multiplique isso com a porcentagem de ousadia. Para fazer isso, selecione a coluna de salário primeiro, mantenha pressionada a tecla de controle. Vá e escolha a coluna de bônus. Dessa forma, você selecionou ambas as colunas. Agora, a partir de Adicionar coluna. Você pode fazer uma operação aritmética de multiplicação. Então, adicione multiplicação padrão de coluna. Isso criará uma multiplicação desses dois números, que é o salário vezes bônus, e depois adicionará isso como membro aqui com o valor do bônus. Podemos mudar isso de volta para cortar e ver se você quer. E então você verá o resultado da multiplicação como um valor monetário de quanto bônus cada pessoa recebe. Vou renomear isso como minha quantia em bolus. Agora que os bônus são calculados, nosso próximo trabalho como parte da análise de dados Leste para dividir esses valores de cálculo de bônus em duas tabelas. Um para todos os nossos funcionários nos EUA e outro para todos os funcionários em locais da Nova Zelândia, faça isso. O que queremos isso em vez de carregar a tabela da equipe como um único item, o que veremos que se eu carregar isso agora perto e Senhor, vamos apenas atualizar a tabela da equipe com os valores em bolus aqui . Você também receberá a tabela de bônus aqui. Essa maneira atual de carregar os dados não é o que queremos. Em vez disso, o que queremos é que não queremos uma única mesa de equipe. Queremos separar as mesas da equipe, uma para todas as localizações dos EUA, ou seja, Bellevue e Los Angeles, depois todas as localizações da Nova Zelândia, que é apenas uma, Wellington, Nova Zelândia. Então, vou voltar à minha consulta de edição. Primeiro passo, vamos dividir esses dados. Para dividir os dados, você tem duas técnicas. número um é que podemos criar uma réplica exata disso. Isso é chamado de duplicação para duplicar uma lei de consulta que eles têm equipe, posso clicar com o botão direito do mouse nela e dizer Duplicar. Vou levar a equipe para a equipe e os funcionários dois são exatamente as mesmas quantidades. Se eu for para a equipe, você pode ver que ele tem todas essas etapas. A equipe também. Também teremos todas essas etapas. Ambos começam com o mesmo arquivo do Excel. Aplique todas as etapas de limpeza para chegar ao estágio final. Então, em cada tabela, posso seguir em frente e fazer os bits necessários. Então, por exemplo, aqui, direi que quero apenas ver minha localização nos EUA. Então, na equipe também, vou desmarcar meu Wellington, Nova Zelândia, e então chamaremos isso hífen da equipe EUA. Podemos ir para a mesa original da equipe. Aqui, vá para o local e desmarque parte dos EUA para que apenas os locais da Nova Zelândia lembrem aqui. E então, e nomeie isso como o hífen da equipe acaba. Essa é uma abordagem. O problema com essa abordagem é que ambas as consultas precisarão ser executadas. Então, o engenheiro da equipe precisa executar todas essas etapas. A equipe USA também executará todas essas etapas, mas isso é meio que duplicando o trabalho e é por isso que ele é chamado de duplicado. Há também outra opção chamada referência. Primeiro, vamos usar a opção duplicada, carregar os dados e depois vou voltar e explicar a você como usar as consultas de referência antes de carregar os dados. Também queremos garantir que a tabela de bônus não seja carregada no Excel. Não precisamos da tabela de bônus para ver na tela. Nós apenas o usamos para fazer as multiplicações. Então, em vez de fechar, Senhor, podemos usar a opção Fechar e Carregar para. Por enquanto. Só vou dizer que só crie conexão. E, em seguida, clique em OK. Neste ponto, teremos todas as três consultas listadas aqui. A equipe USC é a única conexão, mas enzimas e bolus estáveis ou carregados. Agora o que queremos é que não queremos as tabelas em bolus. Estou clicando com o botão direito na tabela de bolus, indo carregar dois. Aqui. Só direi que isso deve ser apenas uma coleção. Não queremos nenhuma tabela, clicaremos em Ok, isso vai me dar um aviso dizendo possível perda de dados. Isso ocorre porque os dados já estão carregados. Isso é bom para mim. Só vou clicar em Ok, e a tabela de bolus agora é apenas uma conexão. Posso excluir esta planilha. E então temos enzimas de pessoal já carregadas. Lei. Vou apenas clicar com o botão direito do mouse e, em seguida, dizer que uma equipe dos EUA, eles também devem ser uma tabela em uma nova planilha. Boom, recebemos duas das consultas elogiadas como tabela e, em seguida, uma outra consulta apenas mantida como uma conexão. Como mencionei anteriormente, embora tudo isso tenha sido bom, tanto a equipe quanto a equipe z bench as consultas dos EUA precisam executar todas as etapas. É aqui que a consulta de referência entra em imagem. Vamos ver como usar a consulta de referência. Para isso, criei um arquivo separado, consulta de referência de dados da equipe 3. E observe que esse arquivo da equipe tem todo o meu pessoal, não apenas os passos da Nova Zelândia. Então aqui, se eu for para o local, posso ver todo mundo. Vamos clicar com o botão direito do mouse e editar essa consulta. E aqui deixaremos a tabela da equipe original como uma provocação e clique com o botão direito sobre ela e faremos uma consulta de referência. Minha equipe original, eles facilitarão a consulta mestre e, em seguida vamos construir uma consulta separada chamada staff e z. observe que staff e z, porque é uma consulta de referência, ele tem apenas uma um único passo. A etapa de origem simplesmente se refere a isso. Essa consulta é a mesma que a tabela de equipe. Dessa forma, essa consulta, a consulta NSAID da equipe não será executada até que a tabela da equipe tenha concluído seu processamento e, em seguida, ela realmente usa esses valores dentro dessa consulta. Agora estou indo para o local e, em seguida, apenas diga, esta é apenas a equipe de Wellington. Esta é a equipe respondida. Faremos referência à equipe original. Então clique com o botão direito e faça referência. Então este chamaremos a equipe dos EUA. Mais uma vez. Aqui vamos aplicar o mesmo tipo de filtragem. Agora temos quatro consultas. A consulta original, meu bônus estável e, em seguida, para consultar a equipe de consultas, NSAID e equipe USE. Vou fazer outra coisa em ambas as ações da minha equipe, NSAID e pessoal dos EUA, o que digamos, para fins de análise de bônus, não precisamos de todas essas colunas. Só precisamos de certas colunas. Podemos ir aos AINEs da equipe para começar e, em seguida, escolher colunas de casa. E então kelp, nossa consulta quais colunas queremos ver na saída final, desmarcarei tudo, manterá o número do funcionário do centro de custo. E então o nome2, que tem meu sobrenome, vírgula, primeiro nome, sexo, salário do departamento. Não precisamos de nenhuma dessas outras coisas, talvez tipo e localização do funcionário, quantidade de bolus. Essas são as únicas colunas que queremos. E isso vai me dar essa saída. Agora eu quero repetir o mesmo passo exato na equipe dos EUA também. Você poderia ir para a equipe USC e, novamente, usar a coisa de escolher colunas. Mas aqui está um pequeno truque sorrateiro. Se você tiver sua barra de fórmulas, isso é algo que você pode usar prontamente para começar, vá para a barra de fórmulas, certificando-se de que você selecionou a opção de outras colunas remotas desta consulta. Então você pode ver que o código de idioma M que estaciona onde ele gerou para manter essas colunas. Então diz que o ponto da tabela seleciona colunas, linhas e essas são as únicas colunas que eu quero. Então, vou selecionar todo esse controle de fórmula C para copiá-lo. E então vou selecionar, vou para a minha mesa da equipe nos EUA. Agora, aqui vamos clicar no botão Efeitos. Isso adicionará uma etapa. E então, nesta etapa, vamos colar essa fórmula. Certifique-se de que não haja mais igual a uma certa alimentação. E, em seguida, pressione Enter. Boom. tabela da minha equipe USA também tem as mesmas colunas. Observação rápida de cautela, porém, esse método não funcionará se ambas as consultas não tiverem exatamente as mesmas etapas na mesma ordem. Isso é tudo. Agora vou dizer Fechar e carregar para. Por enquanto, vamos apenas criar uma conexão. Então, vou carregar esses dados. A enzima pessoal deve se tornar uma tabela na nova planilha. Da mesma forma, a equipe USE também deve se tornar uma tabela na nova planilha. Recebemos a nossa equipe de dados de cortes nos EUA e a equipe inserimos aqui. 6. Automatização de dados: Até agora, só compramos poderia com os dados que estão em um só lugar. Neste vídeo, vamos falar sobre como lidar com cenários em que seus dados podem ser divididos de maneiras diferentes. primeiro exemplo é, em vez de ter todos os dados da equipe em uma única guia em um conjunto contínuo, tenho guias individuais, uma para Wellington, uma para Los Angeles e outra para dados Bellevue. Gostaríamos de combinar tudo em uma grande tabela de funcionários no presente que nos arquivos de saída. Para este exemplo, vou tratar minha equipe no arquivo Penn dot XLSX como o arquivo de dados mestre. Abriremos uma pasta de trabalho em branco. E nesta pasta de trabalho combinaremos todas as três guias individuais de dados usando o método append. Nas partes subseqüentes deste vídeo, mostrarei outras técnicas para combinar os dados. Dependendo de como seus dados estão estruturados e disponíveis. Você pode usar todas essas técnicas individuais como achar melhor. Por enquanto. Vou fechar a tabela APN da minha equipe e voltar para o arquivo em branco. E a partir daqui, digamos dados. Eu queria obter os dados de um arquivo, que por acaso é uma pasta de trabalho do Excel. Mesmo demonstrando a técnica, o arquivo do Excel, quero lembrar que o que quer que você esteja aprendendo, você pode aplicar isso para tabelas de banco de dados ou soluções de nuvem como o Azure ou outras coisas. Ele irá para a pasta de trabalho do Excel e selecionará o arquivo de anexação da equipe. Esses outros arquivos são os que usaremos mais tarde no vídeo. Em seguida, diga Importar. Isso vai me mostrar que existem três planilhas individuais no arquivo. Qual deles você quer trazer. Agora precisamos obter todos esses dados e depois combinar. Então, usarei a opção selecionar vários itens e marcar todas as três guias. Não precisamos carregar esses dados, precisamos transformá-los porque não é assim que eu quero. Quero combinar tudo em uma grande mesa. Entraremos na opção Transformar dados primeiro. Isso abre a tela familiar do editor Power Query com três consultas, uma para Bellevue, uma para Los Angeles e lamenta por Burlington. Todas essas tabelas parecem boas, mas há alguns problemas. Número um, minha linha de cabeçalho está errada. Está dizendo a coluna 12345 em vez da regra número um. Observe que, dependendo de como você configurou suas próprias preferências do Power Query, esse comportamento específico pode ser um pouco diferente para você. Se você já vir a ID do funcionário no cabeçalho da coluna, não precisará fazer esta próxima etapa, caso contrário, você terá que fazê-lo. Que é que queríamos pegar a linha número um aqui, fazer disso um cabeçalho nesta tabela. Então, para fazer isso, podemos ir para a fita Home e, em seguida, usar a opção Foster Row como cabeçalhos aqui. Isso promoverá o cabeçalho da ID do funcionário. Repetirá isso na mesa de Los Angeles e depois na mesa Wellington também. Agora, todas as três tabelas têm os dados com tipos de trade-offs. Neste ponto, eu só queria combinar todas essas três tabelas em uma grande mesa. E é aí que minha funcionalidade de aplicativo e consultas entra em cena. Ele usará as consultas de anexação. Se você clicar diretamente, o que ele vai fazer é anexar os outros dois conjuntos de dados ao conjunto de dados Wellington. Em vez de fazer isso, queríamos criar uma nova consulta que tenha a combinação dessas três tabelas. Usarei a opção extra e, em seguida, selecionarei anexar consultas como Novo. Isso me perguntará como eu quero acrescentar, se fui acrescentar duas tabelas ou três ou mais tabelas, independentemente de qual opção você escolher, o processo é semelhante, mas neste caso nós tem três tabelas. Selecionarei a opção de três ou mais tabelas já que Wellington foi adicionada. Então, vou adicionar meu Los Angeles e Bellevue. Então, quando você clica em OK. Magicamente, todos os dados dessas três tabelas individuais são facilmente combinados em uma tabela gigante que tem todos os dados em um só lugar. Ainda tem os outros problemas, que é meu gênero nulo está lá, meu departamento de treinamento de valores nulos e nulos estão lá e tudo isso. Mas pelo menos está tendo os dados em um só lugar. Neste ponto, você pode seguir em frente e aplicar essas etapas de limpeza. Mas vou renomear essa consulta para mapear dados combinados B12. Em seguida, clique em Fechar e carregar. Esta opção extra fará uma conexão para tudo rapidamente. Isso vai aparecer para conexões. E, em seguida, clique com o botão direito do mouse em dados combinados e altere o comportamento do Senhor para que para que apenas os dados combinados sejam carregados aqui. As tabelas individuais não são carregadas, apenas a tabela combinada. Como mencionei, você pode seguir em frente e aplicar as etapas adicionais de limpeza de dados sobre isso para o nome do seu departamento de gênero obteve ações e tudo isso. Se você quiser. É assim que o método combinar ou anexar funciona. Você pode ter um questionário sobre esse processo de caneta, ou seja, vamos abrir o arquivo primeiro para que possamos obter isso. Observe a ordem das colunas aqui. Tudo tem ID do funcionário, nome, sexo, departamento e termina com o local. Em todas as guias. Você pode pensar, e se minha ordem das colunas for diferente? Não importa em que ordem essas colunas estejam, desde que todas as colunas estejam presentes em todas as tabelas, a ordem não importa. Power Query o reorganizará. Para provar esse ponto, vou à minha tabela de Los Angeles selecionar a coluna do gerente e depois movê-la para cá. Esse local está no meio. O gerente é um fim. Para minha visão de barriga, vou mover uma coluna de gênero até o fim. Excluir. A ordem das colunas é misturada no arquivo de dados original. Vou salvar esse arquivo. Voltamos aqui, clique com o botão direito do mouse, Atualizar e sem erros, nada. Ainda vai funcionar. Ele fornecerá todos os dados como estão nos arquivos originais. Só que o Power Query será reorganizado automaticamente. Agora vem a próxima pergunta, que é e se as colunas não corresponderem? Imagine, por exemplo, aqui no meu arquivo de dados BW da barriga em vez de departamento, vou chamar isso de grupo. Temos um nome separado aqui. Agora vamos ver o que acontece. Vou salvar isso, voltaremos aqui, clique em Atualizar. E então isso adicionará uma coluna extra chamada grupo. Vai manter o departamento, mas vai introduzir uma nova coluna, colocá-la no final. grupo de duto terá valores apenas para O grupo de duto terá valores apenas para as pessoas do sino de visão e aquelas pessoas Bellevue não têm departamento. Portanto, se os nomes das colunas não corresponderem e teremos um problema. Mas se os nomes das colunas corresponderem à ordem dos nomes das colunas, não importa quanto à clareza ajustará automaticamente essas coisas. Essa é uma coisa rápida que você precisa ter em mente sempre que estiver anexando, você precisa ter certeza de que os nomes das colunas correspondem. Então, vamos seguir em frente e corrigir esse problema. Voltarei aqui. Só vou dizer, departamento, salve este arquivo. Volte aqui, clique com o botão direito e atualize. E boom, essa coluna extra agora se foi e os dados estão todos sentados aqui. Bonito e bonito. Permite que a primeira coisa, que é adicionamos usando combinação anexada com o método append. Precisamos saber antecipadamente quantas tabelas estamos anexando. Isso nem sempre é verdade em algumas situações de negócios. Então, para lhe dar uma noção disso, deixe-me mostrar outro exemplo. Aqui eu tenho um arquivo diferente. Em vez de três locais. Sabemos como funcionários extras chegam do nosso escritório da Columbus também. Agora, eu quero combinar todos esses selos em uma grande mesa. O desafio aqui é que podemos usar o método de anexação, porque esse arquivo pode ter alguns aplicativos extras adicionados. Sua consulta não funcionará. Se houver uma nova aba flacidez. Preciso pensar sobre esse problema sob uma luz diferente, seja, eu quero pegar todos esses dados e depois combiná-los. Então, independentemente de quantas guias existem, eu só queria combinar tudo. Deixe-me mostrar-lhe como fazer isso. Novamente, fomos tratados a equipe por local como meu arquivo de dados mestre. Vamos inserir uma pasta de trabalho em branco e aqui combinaremos os dados. Nesta pasta de trabalho. Vou para meus dados, obter os dados do arquivo, da pasta de trabalho e selecionar a equipe por arquivo de localização. Novamente, você pode ficar tentado a selecionar todos os quatro itens, mas, em vez disso, basta escolher um dos itens. Não importa qual item você escolher. Vou escolher minha barriga BYU por enquanto e depois vou transformar dados. Isso só vai me mostrar apenas os dados da guia Exibir da barriga. Neste ponto, precisamos de todos os quatro tipos, não apenas da visão. Então, vou excluir a etapa de navegação e ir apenas para a etapa de origem. Excluiremos a etapa usando esse ícone x-bar ali mesmo. Se você clicar nele, essa forma se foi. No nível de origem, você verá que o Power Query foi cultivado para planilhas diferentes. Maior barriga Bu, mas não vai Wellington, Los Angeles, Columbus e bilabial. Ele também encontrou uma coisa de banco de dados de filtro que não é necessária. Então, o que vou fazer primeiro é filtrar por tipo apenas Folha que estamos olhando para todas as folhas individuais na equipe por pasta de trabalho de localização. Neste ponto, todos os dados de tabelas individuais podem ser acessados com a coisa da tabela aqui. Se eu clicar nisso, você pode ver que todos os meus dados de Wellington aparecem na parte inferior. Tudo por Los Angeles, Columbus em Bellevue data. Aqui. Tudo o que temos que fazer e's, obter esses dados e expandir. Para isso. Apenas selecionaremos o nome, mantenha pressionada, Controlar os dados selecionados. Então, selecionamos apenas essas duas colunas e , em seguida, clique com o botão direito do mouse e, em seguida, apenas dizemos Portanto, não precisamos dessas outras colunas, apenas essas duas. Agora que essas duas colunas estão lá, vou expandir esses dados. Basta dizer colunas de um a 11. Os nomes das colunas realmente não parecem corretos, mas teremos que corrigir esse problema misto. Quando você clicar em OK, você receberá todos os dados de cada uma das planilhas aqui. Dados de Wellington, meu ID de funcionário, nome, gênero, departamento, etc., estão chegando aqui. Esse é o cabeçalho. Assim. Haverá outro cabeçalho mais abaixo da regra de Los Angeles. Assim que começamos Los Angeles e depois compramos mais cabeçalho. E então isso continuará assim. Depois, há alguns nove valores também, o que provavelmente é algumas linhas em branco que ele está buscando. Então, teremos que excluir todas essas coisas. Vamos tornar essa linha em particular um cabeçalho. Neste ponto, dependendo de como seus dados são. Nos meus dados, tenho a localização Wellington, Nova Zelândia marcada aqui, então não preciso dessa coluna de nome, então vou excluir essa coluna também. Em seguida, só temos essa faixa Selecionar From Home use a primeira linha como cabeçalhos. Agora que os cabeçalhos estão prontos, teremos que remover qualquer fonte atual extra do cabeçalho. Então, porque haverá outro ID de funcionário mais abaixo nos dados de Los Angeles se e quando formos lá assim, tem que haver vários funcionários, um para cada planilha. Aqui, tudo o que temos que fazer é usar o filtro. Estou verificando o ID do funcionário. Da mesma forma, vou desmarcar os valores nulos porque se houver alguma linha em branco trazida, não precisamos disso também. Esses filtros vão limpar os dados. E, neste momento, esses são meus dados concluídos. Então, vamos apenas dizer todos os dados como o nome da consulta. E então você pode ir em frente e carregá-lo, ou aplicar algumas etapas adicionais de limpeza de dados. Por exemplo, vamos remover qualquer pessoa no departamento nulo. o botão direito do mouse no sexo, Substitua Sem largura. Outro. Da mesma forma, você também pode usar a data de rescisão para marcar um funcionário como ativo o Senhor e usar o IDE para descobrir se alguém é permanente ou arrependido, em tempo integral ou meio período e usar o salário null e substitua isso pelos 45 mil figurados se você quiser. Vou deixar todas essas coisas para você como problemas extras de lição de casa. Agora que todos os dados estão combinados, posso carregar isso. Diante de Eu Senhor, eu só queria explicar rapidamente novamente o que fizemos. Começamos a partir do arquivo do Excel, depois filtramos apenas as planilhas e, em seguida, apenas expandimos todos os dados. Vamos obter um conjunto de dados que combina dados de planilha individuais em uma meta. Agora, para que essa técnica específica funcione, a ordem das colunas deve corresponder em todas as planilhas. Se as colunas estiverem fora de ordem, isso não funcionará muito bem. Isso ocorre porque quando estamos expandindo, estamos apenas expandindo e combinando a coluna uma de cada folha em uma coluna porque não há cabeçalhos. Por esse motivo. Não corresponderá automaticamente às colunas. Portanto, é por isso que a ordem das colunas deve corresponder quando você estiver usando essa técnica específica. E, finalmente, esta é minha tabela de taxas de saída. Eu posso fechar e carregar isso. Isso só carregará todos os dados em uma tabela bem. Agora vamos ver o que acontece se você quiser introduzir qualquer outra ramificação em uma nova guia nesse arquivo original, vou comprar ir para minha equipe por página de localização. Aqui eu introduzi os dados da filial de Auckland e essas são apenas algumas das pessoas que, da minha filial Bellevue acabei de movê-los para nossa reivindicação. Todos eles têm sua localização e sabemos que uma planilha extra chamada Auckland salvará isso e, em seguida, fechará esse arquivo. Venha aqui, anote todos os locais. Portanto, temos apenas quatro locais. Agora, clique com o botão direito E isso trará essas linhas extras aqui, lei, o número de papéis mudou. E se você for aqui, você pode ver que sabemos como as pessoas de Auckland também adicionaram. Eles provavelmente deveriam estar todo o caminho no final. Como aqui, todas as pessoas do meu outlet estão bem listadas. Anexar, bem como usar essa visualização expandida, são duas maneiras de combinar os dados. Quando você tem dados no mesmo arquivo. Mas muitas vezes seus dados podem não estar no mesmo arquivo. Então, por exemplo, aqui, em vez de ter os dados de incêndio assim, eu posso ter arquivos individuais, um por local terá um arquivo CSV bilabial, arquivo CSV de Los Angeles e um Burlington Arquivo CSV. Cada arquivo me fornece os dados da equipe dentro desse local. Se os dados forem assim, como combinamos? Então esse é nosso próximo tópico. Para que isso funcione, abriremos uma pasta de trabalho em branco e iremos para os dados, obtemos dados do arquivo. E, em vez de usar a opção de pasta de trabalho do Excel, usaremos a opção de pasta. Esta é uma poderosa técnica de automação incorporada diretamente no Power Query usando a qual posso pegar todos os arquivos em uma pasta, combinar todos eles em um conjunto de dados gigante. Então, diremos da pasta, selecione a pasta que seus dados 0s. Aqui, meus dados de localização são a pasta e, em seguida, clique em Abrir. Isso vai mostrar uma tela onde diz, encontrei três arquivos diferentes na pasta, Bella View, Los Angeles e Wellington. O que você quer fazer? Você tem uma opção para combinar. Você também pode carregar ou transformar dados. Como queremos combinar, usaremos essa opção e, em seguida, diremos combinar e transformar dados. Você também pode combinar e carregar diretamente, mas vou usar a opção de transformação porque então podemos ir em frente e fazer um pouco mais de limpeza, se necessário. Farei isso. Então, isso abrirá novamente mais uma tela. E então diz, Harry é a pasta de trabalho rápida dentro de seus quatro patos. Então, ele mostrará um arquivo de amostra. Pode ser o primeiro arquivo, mas você também pode ver qualquer um em qualquer um dos arquivos. E diz, é assim que os dados são. Você está feliz com isso? Se assim for, vou em frente e pegá-lo. Então, tudo parece bom e arrumado para mim aqui. Vou apenas dizer, ok, isso tem que saber, carregar o Power Query, fazer sua mágica e, em seguida combinar todos os dados em um arquivo. Portanto, essa consulta em particular que estamos fora, estamos assistindo ao nó certo E já combinou todos os dados. Então, ele tem meus dados BW da barriga na parte superior. Mas se eu continuar rolando, descobrirei que em todos os meus dados de Los Angeles estão lá. E mais abaixo, teremos nossos dados de Wellington também. Mas vamos entender porque ele teria adicionado muitos outros itens do lado direito ou do lado esquerdo para facilitar esse processo. A forma como o trabalho de automação de pastas aproveita o poder Query construiu várias maneiras diferentes de fazer isso acontecer. Sua consulta final estará na pasta de outras consultas e, em seguida, ela será chamada de dados de localização ou seja qual for o nome da pasta. Mas, para fazer esse processo, ele usará o arquivo de amostra que você viu na tela anterior. Então ele mostrou 11 arquivos na Nazca. Você é isso que você quer? Então, ele usará o arquivo de amostra e, em seguida, criará amostragem transformada. Esta é a consulta de exemplo, portanto, isso está funcionando apenas em um arquivo. Recrie a consulta de um arquivo e execute o mesmo processo em todos os arquivos. Então, se eu quisesse mudar qualquer coisa que eu esteja fazendo em um bom nível, só precisamos fazê-lo na consulta de arquivo de amostra transformada. Por exemplo, se eu vir aqui, posso ver que meus cabeçalhos de coluna estão errados. Poderíamos promover esse papel como cabeçalho, mas ainda teremos que excluir esses cabeçalhos extras em outros arquivos, o que é um problema. Podemos muito bem fazer esse nível de amostra Editar Transformação. Dessa forma, o que acontece é que o Power Query repetirá esse processo em cada um dos arquivos na pasta. Então, iremos para a amostra Transform e aqui aplicaremos a primeira linha dos EUA como cabeçalho de passo que isso se tornará o cabeçalho. Da mesma forma, se quisermos fazer qualquer outra coisa em um nível de arquivo individual, você também pode fazer esses processos aqui. Como, por exemplo, removidos funcionários nulos. Uma vez que fizermos a cadeia no arquivo Bellevue, se eu for para meus dados de localização, você verá que ela fez o mesmo para todos os outros arquivos. Portanto, embora apenas os dados BW melhores sejam o que limpamos, Power Query reutiliza esse processo e seguida, aplica isso no meu arquivo de Los Angeles. Na data de Los Angeles. Além disso, haverá outro gênero e ninguém mais estará no departamento nulo. Porque ainda existem alguns departamentos em branco. Vou desmarcar isso. Essas pessoas se foram. E isso é praticamente isso. Nossos dados agora estão limpos e podemos ir em frente e carregar isso. Então, novamente, vamos apenas dizer tills e Load para criar uma conexão por enquanto. Clique com o botão direito nos dados de localização carregue em uma tabela Somente esse crédito é carregado. Nenhuma dessas outras consultas intermediárias é carregada. Todos eles são mantidos como conexões. Esta é uma técnica de automação muito poderosa. O que isso significa é que se você quiser adicionar arquivos extras a essa pasta, Atualize esse processo. Ele vai pegar esses arquivos e adicioná-los no final. Não vou demonstrar isso, mas vou deixar isso para você como um exercício extra de lição de casa. Então lá vai você, três maneiras diferentes combinar os dados. Um usando a consulta anexar é a opção um usando a opção de combinação de várias folhas e outra usando a opção combinada de pasta. Dependendo de como seus dados são. Use uma dessas três técnicas para consolidar os dados e degenerar conjuntos de dados maiores com os quais você deseja trabalhar. 7. Dados de: Neste vídeo, vamos falar sobre uma das técnicas mais poderosas do Power Query. É a capacidade de desescolher quais dados. Lembre-se do nosso problema de cálculo de exemplo de bônus. Tivemos bônus definidos uma porcentagem de bolus por departamento. Mas e se você tiver bônus definidos assim? Aqui eu tenho um estilo de matriz de alocação de bônus para cada departamento, cada categoria de equipe, sabemos qual porcentagem de bônus deve ser alocada. É aqui que tínhamos ido descobrir duas técnicas diferentes. Número um, como pegar esses dados e remodelá-los em um formato que funcione melhor para fazer o cálculo do bônus. E a técnica número dois facilita as mesas de almoço em várias colunas. Vamos seguir em frente e fazer isso. Vou fechar esse arquivo. Esta é uma das pastas de trabalho anteriores. Portanto, esta não é a pasta de trabalho que temos da combinação de pastas ou qualquer coisa assim. Esta é a pasta de trabalho da lição número dois, mas não importa. Você também pode usar os dados originais e ainda fazer esse tipo de coisa porque temos a coluna do gerente bem como as colunas do departamento ou ambas as colunas são necessárias para descobrir que porcentagem de bônus para o nosso quadro. Aqui vou apenas para Dados e, em seguida, obter dados do arquivo, da pasta de trabalho do Excel. E aponte para o meu mapeamento de bônus para arquivo que tem as informações de mapeamento magnético extra estabilizado. Vamos clicar em OK mirar. Isso abrirá o Power BI, navegador Power Query e em seguida, pergunta, o que você quer? Existem algumas coisas diferentes. Temos nossa tabela de bônus principal. Há também um intervalo nomeado chamado matriz de pontos bônus que também aparece aqui. Vou escolher a tabela porque esse é o formato correto para nossa situação e depois entrar em dados transformados. Isso traz a mesa aqui muito bem. Embora esse tipo de tabela seja muito bom para manter as informações, não é muito bom para calcular o bônus. Então, se eu for para a minha tabela de funcionários, você percebe que temos nossa coluna de departamento, bem como a coluna de informações da equipe como que tipo de gerente eles são, se eles são gerentes em um coluna de gerente ou equipe ou gerente. Precisamos usar essas duas colunas para descobrir qual porcentagem de bolus cada funcionário está recebendo. A orientação das informações aqui O departamento IS tem sua própria coluna, gerente tem sua própria coluna. Enquanto este mosteiro ouvirá que o departamento tem sua própria coluna, mas o gerente meio que atravessa a tela. Esse tipo de formato é chamado de formato TED dinâmico. Parece uma tabela dinâmica. A partir disso, para mapear os dados, precisamos fomentar e girar os dados. A consulta de peças oferece muitos recursos poderosos e dinamizar os dados para desselecionar o conjunto de dados como este, o que você deseja fazer é clicar na coluna do departamento e clicar com o botão direito do mouse. Você já precisa selecionar a coluna do departamento e depois dizer não pago sobre outras colunas. Isso vai manter a coluna do departamento e, em seguida, pegar o resto dela, um não-giratório, você secretamente o que ela faz quando você se inscreveu. Então, ele vai dividir isso em três colunas. Então, sua tabela original agora está remapeando-a para três colunas diferentes. Um departamento mais fácil que é retido. A próxima é a coluna de atributo, que lhe dirá que tipo de pessoa eles são e que porcentagem de bônus eles estão recebendo. Todas as combinações são apresentadas aqui. Agora que a formatação está certa, posso mapeá-la se quiser. Posso renomear atributo para gerenciar nosso ponto de interrogação. Mas isso não é necessário porque não o fazemos, não usaremos esta tabela. Estaremos calculando diretamente o bolus. Agora que o Dan pivoting está pronto, vamos em frente e mapeá-lo. Antes de saltarmos para lá, eu só quero destacar as outras opções e pivô. Então, se eu clicar com o botão direito do mouse aqui, você pode ver que temos três tipos de pivô. Então, dependendo de como seus dados são, você pode tentar um desses pivôs e todos eles funcionam no mesmo princípio. Mas, dependendo do que você selecionar as alterações do resultado do Done Pivot. Nesse caso, outras colunas desfavoráveis era a opção para nós que, quando conseguimos manter a coluna do departamento e o tipo de alcance, reorganizamos todo o resto. Vamos voltar aqui e agora vamos usar as consultas de correspondência inicial. Escolha a coluna do departamento, mantenha pressionado o controle e escolha a coluna do gerente. A ordem na qual você escolhe também é assunto departamento é a primeira coluna, gerente é a segunda coluna. Mapeamos assim. Vou escolher meu gerente de controle do Departamento de Estado de bônus. Agora ambas as colunas mapearam 11 CO2. O que Park onde ele vai fazer agora é pegar a combinação de gerente de treinamento e, em seguida, ele lhe dará um gerente de treinamento, 0,2 como saída. Então, ele cuidará de todas as combinações e como mapeá-las corretamente. Assim que isso for feito, clicaremos em OK. Receberemos a mesma tabela de bônus antiga para cada linha. Isso me diz qual é a porcentagem dessa pessoa. Vamos apenas expandir isso, desmarcar essas opções para que apenas obtenhamos o valor. Não precisamos ver o departamento e o gerente novamente. E há minha porcentagem de bônus. Agora que as porcentagens aqui eu posso calcular o valor do Bolus selecionando a coluna de salário, mantenha pressionada o controle e selecionando a coluna de valor. Agora que ambas as colunas estão selecionadas, adicionaremos o padrão de coluna, multiplicaremos. Boom, recebemos nossas informações de bônus aqui que vêm dos dados da matriz. Aqui eu posso renomear isso como dólar em bolus. Então eu não quero mais esse valor. Também posso remover esta coluna. Vou manter esse valor lá para que possamos nos referir a ele quando os dados forem enviados para o Excel. Agora que o nosso eu estou girando está pronto, vou dizer Fechar e carregar, carregar ferramenta e apenas dizer conexão. Dessa forma, minha tabela de bônus não é carregada aqui, mas a tabela original da equipe agora tem esse valor extra de colunas e o valor bônus é minha porcentagem de bônus, e o dólar de bônus é a informação do meu dólar. Se desejar, você também pode aplicar alguma formatação adicional ou um arredondamento dos valores dentro do Power Query. Ou quando os valores estiverem no Excel, você pode fazer o que quiser. Esses dados, esse é um tipo de na puberdade, mas estou girando é bastante útil. Então eu pensei que vamos dar uma olhada em outro exemplo de VPL no último exemplo deste curso. Para isso, tenho um arquivo separado por completo. Vou salvar isso e abrir esse arquivo. Eu chamo isso de pivô de caça. Este é um bom desafio. Se você está se sentindo um pouco aventureiro, pause o vídeo aqui e vá em frente e não gire para esses dados. Mas se você ainda está chegando a um acordo com todo o conceito não pivô, vamos entrar neste exemplo específico. Estamos fazendo uma semana de jogos corporativos em chocolates incríveis. E esta semana de shows corporativos está acontecendo entre 11 de abril de dois, 15 de abril, naquela semana inteira, pouco antes da Páscoa. Temos alguns de nossos funcionários jogando vários tipos de jogos. Por exemplo, na segunda-feira 11 de abril, estamos jogando críquete. E essas são as pessoas que estão jogando críquete na semana de jogos corporativos em 12 de abril. Essas são as pessoas que estão jogando vôlei. Essas pessoas estão pagando basquete, estes e cabo de guerra. Essas pessoas estão jogando. Então, temos dados de estilo de matriz. Você pode pensar nisso como dados de estilo de tabela dinâmica. O grande problema é que cada célula em si contém vários nomes. Se eu quisesse fazer uma pergunta como, quantos jogos Michael está jogando ou quantos indivíduos estão jogando vôlei? Se a mesma pessoa jogar várias vezes e não queremos contá-las. Se, por exemplo, Phyllis está jogando vôlei na segunda-feira, ela está novamente jogando na quarta e na sexta-feira. Portanto, não queremos contar o preenchimento é tentativas. Só queremos contar com ela uma vez. Como você responde a essas perguntas? Torna-se super impossível com esse tipo de formato. Enquanto que se eu tiver um formato tabular com esporte, data, nome e três colunas, então poderíamos fazê-lo. Então, formato regular em vez de um formato dinâmico, vou selecionar todos esses dados. Eu não queria transformar isso em uma mesa. Enquanto que se eu tentar colocar isso no Power Query, ele dirá que você precisa ter os dados como uma tabela. Portanto, outra alternativa é, se você estiver trabalhando com os conjuntos de dados como este, selecione os dados e dê a eles um nome na caixa de nome. Você pode fazer isso daqui ou nome definido pela fórmula. Qualquer uma dessas opções funciona. Vou nomear isso como minha semana de jogos corporativos, memes de ponto CDW. Depois de tê-lo como um intervalo nomeado, você pode ir para os dados da opção Intervalo de tabela e o Power Query não solicitará que você crie uma tabela. Os dados estão chegando aqui bem e limpos. Sabemos que esta linha é a linha de cabeçalho, então vou usar apenas para cabeçalhos de estresse que levamos isso para o cabeçalho. Agora que isso está feito, vamos manter o esporte e girar para isso. Então clique com o botão direito do mouse sobre isso e gire outras colunas. Então, obtemos valor de atributo esportivo. Posso namorar belezas. Então, vou dizer que este é o meu encontro. Então o valor tem meus nomes. Observe que cada célula teria todos os nomes separados por vírgula. Aqui está uma coisa boa. Seguimos uma formatação consistente que os nomes são sempre separados por uma vírgula e, em seguida, há um espaço. Então, o delimitador tecnicamente é vírgula e espaço. Se pudéssemos pegar cada nome e depois derramá-lo como críquete, 11 de abril, Erin Karen, David, Andy assim. Separe linhas, então nossos dados estão em boa forma. Podemos ir e analisá-lo. Portanto, tudo o que temos que fazer é clicar com o botão direito do mouse na coluna de valor e depois dividir a coluna por um delimitador. Aqui em vez de vírgula, vou dizer Custom e depois dizer. Espaço vírgula. Então esse é todo o delimitador. Então, por padrão, se eu clicar em OK aqui, vou obter colunas individuais, uma por pessoa. E isso também nos torna impossível fazer perguntas como quem todos estão jogando vôlei, porque então teríamos que acreditar com várias colunas. Alguns deles terão valores nulos porque só temos quatro pessoas jogando isso naquele dia. Isso não vai cortá-lo. Eu excluirei essa etapa usando essa marca X voltará aqui, clique com o botão direito do mouse em dividir coluna e por delimitador. E, em seguida, espaço de vírgula personalizado em cada atualização em algum delimitador. E então usamos a opção avançada. E então, em vez de dividi-lo em colunas, dirá, divida isso em linhas. É isso. Dessa forma, o que ele vai fazer é dividir isso em linhas individuais e tudo vai funcionar bem e bonito. Clique pronto em OK. Sua tabela inteira é expandida e você obterá uma tabela maior, originando-a a partir dos dados originais. Então, temos sua data e valor esportivos. Nomeie isso como pessoa. Vou clicar com o botão direito do mouse na data, alterar o tipo para data também para que isso apareça como um erro. Isso é interessante. Quero dizer, isso não é algo que eu estava contando em encontrar no vídeo nem explicá-lo. Mas agora que há lá, vamos seguir em frente e ver. Só vou apontar esse erro. Não foi possível passar a entrada fornecida como um valor de data. Então, para 11202212 AM, acho que o raciocínio é esse formato é mês, dia, ano, enquanto minha Power Query provavelmente está procurando o formato dia, mês, ano ou ano, mês dia. Portanto, não está muito feliz com esse formato. Podemos voltar e excluir isso para que possamos analisar a mudança de tipo, mas também vou desfazer a alteração no tipo. E vou te mostrar uma técnica adulta. Podemos clicar com o botão direito do mouse na data e , em seguida, ir para alterar o tipo em vez de data. Vou usar essa última opção usando o local. Sempre que você tiver datas que não são solicitadas, mas você é um formato padrão do sistema. É uma boa ideia usar a opção local porque dessa forma você pode dizer ao Excel que está lidando com os dados que são para o formato de data padrão do Medicaid e de outros países. Aqui meu tipo de dados será um encontro. Se eu deixá-lo aqui, inglês, Estados Unidos, você pode ver que a formatação padrão do inglês Estados Unidos facilita o mês, o mês, a data e o ano. Portanto, esta é a formatação. Acho que isso deve funcionar. Não funciona tão bem. Eu suspeito porque nossa data também tem esse componente de tempo. Isso não fazia parte da planilha, mas alguns esperam que nossa consulta esteja pensando que isso é exatamente à meia-noite. É aí que as coisas estão entrando em um pouco de confusão. Então, talvez se formos ao tipo de dados de alteração com localidade e usarmos esse ícone de engrenagem. E a partir da data do tipo de dados, se eu colocá-lo como barra de data, hora e clique, OK, isso funciona. Aqui eu tenho agora valor de caráter, data e hora. Não precisamos do tempo porque o tempo realmente não tem significado. Então, agora que ele é tratado como dados, posso converter isso de volta ao momento sozinho e acho que Bush gap. Então, primeiro tomamos como data e hora e depois o pressionamos para se tornar uma data. E isso parece fazer Park onde ele feliz. Algumas das conversões de data e hora são uma letra complicada, dependendo de como seus dados são formatados. E é aqui que acho que o Parkway oferece um pouco mais de poder e capacidade de processar os dados em vez do Excel sozinho. Então, entender essas nuances pode percorrer um longo caminho trabalhar com tipos de dados desafiadores que você encontrará de tempos em tempos. Então, um bom desafio sobre o qual eu nem estava planejando falar. Mas a data esportiva e a pessoa agora estão aqui. Eu posso fechar e Senhor, vou ter uma boa mesinha aqui, a mesa verde que me diz quem está jogando, o quê. Dada esta tabela, podemos fazer uma tabela dinâmica rápida a partir dela. Então, vou inserir um pivô desses dados aqui. Por exemplo, para ver quem está jogando ala. Então, se eu escolhi um esporte e depois coloquei a pessoa, pegue a contagem de pessoas. Então, é assim que muitas pessoas estão jogando cada esporte. Você pode ver que, por exemplo, voleibol é o nosso esporte mais popular com 30 pessoas jogando. Em seguida, é cabo de guerra. Descontos, duplicatas, mas você também pode contar os valores exclusivos ou o que quiser. Você também pode, em vez de esporte, você pode ir e colocar Parson e depois chegar ao número de esportes que eles estão jogando. Jan tem Johnny jogando em cinco ocasiões diferentes. Algumas de nossas pessoas estão pagando nove vezes. E se eu resolver isso, posso ver Kelly e Stanley. Algumas dessas pessoas são bastante atléticas e Andy só joga duas vezes em todas as coisas. Esta é uma maneira muito boa de analisar os dados agora que estão em um formato melhor, não seríamos capazes de obter esse tipo de coisas daqui. Vamos testar isso rapidamente. Se eu mudar alguma coisa, quer isso apareça lá ou não, vou fazer algumas anotações aqui. Por exemplo, Dwight exibindo apenas duas vezes, Michael só está pagando três vezes. Vou adicionar os nomes deles a alguns dos outros lugares e depois veremos o que acontece. Então aqui vou dizer Dwight. Então este, vamos colocar branco e Michael. Ambos. Nós adicionamos um que eu deveria acrescentar, e Michael deveria ter subido por um. Depois que esses dados forem alterados virão aqui, clicaremos com o botão direito do mouse e atualizaremos. Isso vai atualizar esta tabela. E então ele adicionará as informações extras algumas lá. Mas essa tabela dinâmica não será atualizada porque ainda diz árvore Y2 Michael. Agora saiba que os dados são atualizados. Você precisa atualizar isso e então isso vai passar para Ford Michael para bifurcar. É aqui que você precisa atualizar duas vezes devido à forma como essa coisa está configurada, você é uma atualização mais rápida. Obteremos os dados atualizados e, em seguida, sua segunda iteração atualizará as tabelas dinâmicas. Você também pode usar algumas das configurações dentro do Power Query para atrasar a atualização da tabela dinâmica até que a atualização de dados esteja concluída, que seremos maravilhosos. Ela é uma F, mas deixo todas essas coisas para você descobrir por conta própria. Lá vai você. Estou girando algo um pouco mais complicado do que os conjuntos de dados habituais que você vê. 8. Aulas bônus automatizar coisas com consulta Power Query: Oi lá. Este é um vídeo bônus no minicurso Power Query. Neste vídeo, vou falar sobre como podemos fazer uma tarefa de navegação e dados altamente repetitivos e, em seguida, usar o power Query para automatizá-lo para que você possa simplesmente sentar e relaxar. Na verdade, isso não é como o resto dos vídeos da classe, porque esse vídeo é extraído de uma transmissão ao vivo que eu fiz no meu canal do YouTube. No meu canal do YouTube, faço transmissões ao vivo todos os meses. Portanto, o tópico deste mês é como automatizar tarefas chatas e repetitivas. E eu peguei a transmissão ao vivo, editei as partes que não são relevantes para esta lição e depois meio que encolhi em uma sobre o vídeo. Embora isso seja um pouco mais longo do que o resto dos vídeos da classe, eu encorajo você a assistir a tudo isso porque há muito valor lá dentro. E quando você terminar este vídeo em particular, você será como, Oh meu Deus, eu posso usar o power Query para fazer tantas coisas mais incríveis. Então, vá em frente e assista a esses arquivos de amostra de dados que você pode baixar e praticar o conceito. Ou recomendo que você os aplique às suas situações de trabalho também. Sim. Muito obrigado. Vou te pegar no vídeo. Olá, todos. Bom dia. Boa tarde, boa noite. É tão bom tê-lo em nossa transmissão ao vivo automatizada e chata de dados para o mês. Estou muito animado. Vamos entrar na sessão. Você pode pegar os arquivos, como mencionei no link de descrição do vídeo que está lá. Eu vou. Ao longo de chegar ao problema. Esse problema se originou com uma das situações da incrível empresa de chocolates. chocolates impressionantes são uma empresa inventada que uso em muitos dos meus vídeos e exemplos. E este também é um dos cenários que criei para nós em chocolates. Então, o que aconteceu em incríveis Cs de chocolate? Vendemos chocolates e somos um chocolate, não uma empresa de chocolate de varejo, mas a empresa grossista de chocolate. Fazemos duto de chocolate eventualmente ser vendido em supermercados para seus clientes. Muitas vezes, nossos pedidos de chocolate vêm de supermercado a granel e acabamos de enviá-los caixas. Mas a maioria desses artistas está automatizando isso. Mas às vezes o que acontece é que nossos clientes também recebem um formulário de pedido tradicional para os chocolates. Então, um dos outros formulários se parece com isso. Então, aqui neste desafio em particular, mostrarei como construir com dados em situações em que as coisas podem ser um pouco mais não convencionais e como lidar com isso. Então, como você pode ver, este é o meu formulário de pedido de cliente, mas depois chocolate incrível porque não parece bastante limpo. Portanto, não é como dados super sujos que estamos falando. Mas é, você entenderá rapidamente que esse tipo de formato é um problema para se trabalhar se você quiser fazer algo de forma significativa, quando se trata de fazer análise de dados, amplie rapidamente para que possamos ver o que está acontecendo aqui. O formulário de pedido tem um tipo de modelo padrão, que tem o nome do vendedor e o nome do cliente. Então aqui temos a barra de botões areias, engraçado e cliente, algum vencedor ousado. Seus detalhes são mencionados apenas uma vez no formulário de pedido. E depois mais abaixo, temos todos esses produtos sábios, quantas caixas eles não estavam em cada um dos dias. As datas em si podem não ser contínuas como você vê aqui. Segundo de maio, 114 caixas de barras de frutas e nozes. Então, quinto de maio, e eles não eram 108 de 99%, puros, escuros e puros assim. As datas vão assim. Às vezes, serão datas de até 1515 dias diferentes porque este formulário de pedido, você só pode inserir até 15 dias de dados. Então, é como uma forma de arte tradicional. Algumas de nossas reivindicações preenchem e enviam para os vendedores, e então teremos que atender aos pedidos. Agora, o grande desafio, é aqui que você e eu ficamos à vontade para analisarmos esses dados e fazer algum trabalho de análise. O problema aqui é que os dados estão todos lá. Por exemplo, se eu quisesse saber quantos chocolate de menta total precisamos enviar, poderíamos fazer uma análise simples de adicionar o mapa de colunas, mas estamos adicionando essa regra para cima e depois dizendo qualquer que seja esse número, seiscentas, seiscentas caixas. Mas o problema, por causa da forma como é estruturado, fica um pouco desajeitado e a Sra. Messy, não sabemos o quão longe para alguns e tudo isso. E se eu tenho várias formas de arte do bar engraçado em arquivos diferentes, então não sabemos exatamente como agir e é aí que os desafios são uma forma de ordem. Mas, como eu mencionei, temos centenas de ohms. Portanto, esta é a pasta onde eu mantive alguns são seguidos formulários de ordem. E agora estou Nesta sessão, vamos entender como você pode pegar dados que meio confusos, confusos e distorcidos assim. E então, como você pode criar um sistema através do qual você pode combinar todos esses dados em uma meta em uma tabela mestre final. Isso é realmente o que a sessão ao vivo vai ser tudo. Vamos pegar os arquivos, construiremos um processo repetitivo que apenas obterá todos os dados, certifique-se de que não seja um formato de tabela única no final dele. que qualquer número de arquivos que você alimenta para ele, ele só pegará tudo e, em seguida, combinando, coloque-o em um só lugar. Se você acha que é a coisa mais incrível você ouviu durante toda a semana, dia todo ou todo o ouvido. Por favor, fique falso, permaneça e aproveite o resto da sessão se você já conhece algumas partes dela, mas não todas. Mais uma vez, autônomo. No entanto. Isso é que vou entrar em ampliar a tela e garantir que você possa ler as coisas mais tarde. Mas agora, porque estou apenas rebaixando o arquivo, não há muito para realmente estranho. Não estou fazendo nada. Os arquivos já são fornecidos a você. Estes são os arquivos que eu forneci quando você baixa os arquivos, arquivo zip que contém todas essas formas de arte e, em tudo, falarei rapidamente sobre o resto dos arquivos no arquivo. Finalmente, os arquivos que eu lhe dei, há também uma pasta de trabalho do Excel de dados combinada que tem a saída final do Excel que tem todos os dados combinados. Se você quisesse ver como finalizá-lo . A saída parece assim. Esse é o arquivo, mas é o que vamos construir nos próximos 60 a 90 minutos. Um longo caminho. Você vai aprender alguns, alguns recursos realmente poderosos. Então você entenderá como o Power Query funciona. Você entenderá como construir uma função dentro do Power Query. Esta é uma das funcionalidades mais poderosas e avançadas do Power Query. E então, como executar essa função repetidas vezes. Algumas das melhores práticas quando se trata de construir essas funções e usar tudo isso. O que vou fazer é abrir o Excel. E então este é apenas um arquivo em branco. E então, a partir deste espaço em branco, enquanto vamos reunir todos os dados no saco final em branco. Bem, isso é realmente o que nós, o que precisamos alcançar. Então, mentir silenciosamente no Excel e, a partir daqui, é processo bastante simples. A primeira coisa que queremos fazer é trazer os arquivos aqui e depois combiná-los. Vou mostrar a você como os processos para um único arquivo primeiro, porque então você entenderá o verdadeiro poder do Power Query. Então os homens eram do primeiro arquivo, você entenderá certos conceitos-chave e então veremos como repetir esse processo para todo o arquivo. Essa é realmente assim que você fica meio que uma repetição disso. E então, se algo fosse confuso na primeira vez, segunda vez ficará claro. Não conseguimos ir para a faixa de dados. E a partir daqui você pode acessar todas as funcionalidades relacionadas ao Power Query. Então eles estão sentados aqui neste cantinho chamado obter e transformar dados. Em nossos dados é, mesmo que esteja um pouco confuso, está tudo em planilhas, então é fácil para mim apenas obter os dados do arquivo da pasta de trabalho do Excel. Então esse é realmente o caminho se conectará a isso. Então aqui você só quer apontar para o local do arquivo onde o arquivo individual é não, neste momento só vamos fazer o processo para um único arquivo. Então vamos repetir o processo para todo o grupo. Mais uma vez. Vou apenas ir para minha pasta de transmissão ao vivo, pedir formulários e depois escolher qualquer formulário de pedido. Isso não importa. Por uma questão de simplicidade e consistência, vou escolher os zeros, 01, o primeiro começo. Em seguida, clique em Importar. Isso foi mais longe, abra na tela do Navegador. E, dentro da tela do navegador, agora você pode fazer uma visualização dos dados que o Power Query mostrará que esses são os dados que encontrei. Você está feliz com isso? Outra nota importante aqui, o que quer que você faça dentro do Power Query, você, mesmo que eu esteja rebaixando isso com um arquivo do Excel como dados de origem, você pode aplicar a mesma ideia, os mesmos conceitos para seus conjuntos de dados SQL para seus arquivos de texto, para suas páginas da Web ou para seus arquivos XML, independentemente dos dados de origem, você pode aplicar isso. Então não pense, Oh, esse incrível exemplo de chocolates não é para mim. Agora, esse não é o caso porque a técnica e a aplicação e a gravidade da doença são tão poderosas que, se você ignorar essa criatura do Excel, você estará fazendo muito trabalho repetitivo manualmente ou usando algumas outras tecnologias mais antigas. Isso é muito importante aqui. E o Navegador, apenas selecionamos isso e, em seguida, ele mostrará como os dados se parecem. Para o navegador. Como você pode ver, este é um único arquivo realmente confuso . Para nossos olhos, parece super limpo, mas quando estamos olhando para ele da perspectiva do Power Query, só vê os dados. São muitos valores nulos. É tudo incompatibilidade e tudo o que precisamos para fazer a etapa de dados transformados. Se eu carregasse diretamente, será inútil para mim. Não consigo fazer nenhuma análise para este arquivo sozinho. Esqueça de fazer isso para centenas de arquivos. Precisamos usar o botão Transformar dados. Vou clicar nisso e ele meio que entrará na tela do editor de consultas dentro do Power Query. E isso é um atalho. Isso é algo que não faz parte da sessão. Mas se você vê coisas no Power Query e está pensando, Oh, tudo é super pequeno aqui. Como eu presumo? Porque dentro do Excel posso segurar o botão do mouse e, em seguida, para cima e para baixo e aumentará a tela. Você pode usar o botão Control plus, controlar menos para apenas o zoom da tela. Este é o Power Query. Como eu disse, se você nunca fez nenhum trabalho de Query power, então pode ser um pouco confuso qual é a tarefa que vamos fazer agora, mas bem, vou tentar explicar um breve nível. Esta é uma tela do editor de consultas através da qual posso dizer ao Power Query como quero que meus dados sejam limpos. A qualquer momento, você pode ter várias consultas. É por isso que este painel aqui lista todas as consultas. Agora mesmo. Temos apenas 11 consultas, então esta é a única que está listada aqui, c onde f, Mas se você tiver várias consultas, todas elas virão aqui. E não tem o material de fita habitual no topo. É toda minha fita. E você pode ler coisas e entender que há alguns botões importantes aqui. Fechar e carregar significa que você terminou seu processo, você queria saber, carregar os dados no Excel. Em. Consultas de mesclagem significa que eu queria combinar duas consultas diferentes, como uma operação de junção. Anexar significa que quero combinar dados um depois disso, como costurar em tudo isso. Dentro da consulta, essa caixa grande aqui, ela me mostra como meus dados se parecem. Naquele momento. Você pode aplicar diferentes etapas nos dados. Quaisquer que sejam as etapas aplicadas, elas aparecerão aqui nessas etapas da planta. Neste momento, não fizemos nada. Então, tudo isso está realmente em branco. Tudo o que fizemos foi carregar o arquivo de origem e depois navegar. Mas ele era basicamente o que a tela dos navegadores estava fazendo. É realmente onde estamos e qualquer nome para sua consulta, você pode dar, você pode mudar tudo isso, o que quer que você faça aqui. Ele só se aplicará localmente neste arquivo do Excel. Isso não mudará a fonte. Bem, o arquivo fonte ainda estará como está, mas este terá todas essas coisas extras adicionadas. Por exemplo, vejo que a coluna 12 é nula. Não há nada lá, é apenas nulo. Assim, posso manter pressionada minha tecla Shift, selecionar ambas as colunas e mouse e, em seguida, basta dizer remover colunas. Como eu disse, o que isso fará é apenas remover essas colunas localmente para mim, mas o arquivo original exige como está. Agora, há uma bagagem de passo de colunas remota aqui, que simplesmente diz remover duas colunas. Da mesma forma, você pode ver que a primeira linha e a segunda linha, tipo de não há realmente nada lá. Ele diz o formulário de pedido do cliente. Interrompa para 15 dias do pedido e, em seguida, a terceira linha também é nula. Portanto, os dados reais começam a partir da regra número quatro em diante. Poderíamos, por exemplo, levar as três primeiras linhas e invadiríamos a letra, realmente perder qualquer coisa valiosa porque não há nada de interessante lá. Então, posso usar o botão Linhas de remoção aqui. E então apenas diga o topo remoto. Então diga, o número de linhas é três. Agora boom, esses papéis também se foram. Essencialmente, adicionamos um passo extra que diz, eu não preciso dessas três principais linhas. Livre-se de Decker. Digamos que tudo o que eles querem. Quando limpo esses dados, quero saber quem é o vendedor e quem é o cliente. Não me importo com os números e tudo mais. Nós só queremos linhas 12, tudo o resto é irrelevante para nós. Então eu posso usar o botão de navegação da tecla, manter as linhas superiores. Então, antes, usamos remoções. Agora estamos usando as linhas de manutenção. Linhas, apenas mantenha. Rosenhan escreveu os números 12 porque é aí que minhas informações de vendedor e cliente estariam. Neste ponto. Mais uma vez, não precisamos de nada. Nenhuma dessas outras colunas é necessária. Somente as duas primeiras colunas são o que precisamos. Então, posso usar o Shift e selecionar a coluna 34. Clique com o botão direito, remova outras colunas Isso significa mantê-los e se livrar de todo o resto. É assim que basicamente estamos transformando os dados. Retiramos a parte dos dados de tudo o que está lá. Neste ponto, os dados vão para o lado. Vendedor, cliente, idealmente, quero que o vendedor e o cliente tenha duas colunas. Podemos ir para a fita Transform e, a partir daqui, há um botão de transposição. O que ele vai fazer é virar a mesa de lado. Se eu fizer isso assim, os dados mudarão sua orientação. Então agora temos Bonferroni aqui e embarcar se ele lá. Se eu voltar a uma etapa anterior, você verá que é o quê? É horizontal. Desta vez, agora sabemos que a coluna um deve ser chamada de RUIM, engraçado e a coluna dois deve ser chamada cliente, vendedor e cliente. Novamente, de casa, podemos usar a primeira linha dos EUA como opção de cabeçalhos para definir os caracteres, ou seja, ponto e vírgula. Depois disso, posso simplesmente excluir isso. Essa etapa é chamada de renomear as colunas. Então, renomeamos. Pegamos um monte de dados, um monte de dados, e depois aplicamos um passo após o outro, de modo que, no final de tudo, conseguimos extrair as duas informações. Imagine o outro arquivo, como uma montanha de realmente mudar a informação. Conseguimos entrar, cavar lá em cima e, em seguida, encontrar os dois itens de informações valiosas no próximo arrasto para cima. Este é apenas um exemplo de caso, mas meio que lhe dá uma noção do que estamos alcançando. Neste ponto. Estou feliz com a forma como as coisas estão. Só posso dizer Close and Lord. E isso foi para o Flickr na tela algumas vezes e depois me trará os dados finais disso. Há apenas uma informação de linha aqui. É óbvio que só haverá uma linha porque um no outro há apenas um cliente em um vendedor. E eles virão aqui. Lembra de onde tudo começou? Começou a partir da forma como o arquivo f 001. Agora vou voltar rapidamente aqui e abrir meu arquivo. Este é o arquivo fonte original, e vou mudar algumas coisas daqui. Então, em vez de bar engraçado, vou digitar meu nome lá. Em vez de o vencedor do conselho digitará. Blanking aqui. W. Esses dois nomes, estes não existem em nenhum outro lugar nos dados, então é fácil para nós identificar. Vou salvar esse arquivo. Feche isso. Agora, aqui, se eu quiser obter os dados do caractere agora que o arquivo mudou, é uma questão severamente simples de clicar com o botão direito do mouse, atualizar. E boom, obtemos os dados atualizados aqui. Nenhuma dessas etapas precisa ser repetida novamente, porque o que fizemos essencialmente é que ensinamos ao Excel como queremos fazer esse processo. Quando os dados mudam, posso voltar e fornecer os dados atualizados. Embora seja tudo super bonito e incrível, existem algumas desvantagens nessa abordagem. A maior coisa que você precisa lembrar é poderíamos com várias suposições para chegar aqui. Por exemplo, se eu for aqui, observe que removemos as três primeiras linhas porque elas estão todas em branco. Mas digamos que recebamos um arquivo de pedido onde falta o número três. Assim. Salve isso, atualize. Provavelmente o arquivo está fechado. Sim. Já recebo um erro. Não está me dizendo que há um erro, mas isso é realmente o que está acontecendo porque estava tentando fazer alguma coisa, mas isso realmente não acontece. Posso, por exemplo, ir AT dez, descobrir o que estava acontecendo. Mas porque estamos trabalhando sob a suposição aqui, há um erro. Você pode ver que em cada etapa que está disponível dados, em algum momento, as coisas teriam novo o anterior aqui. Em cada etapa, os dados estão disponíveis até esse ponto e, em seguida, meu nome desapareceu completamente. Só não estamos à altura das informações do cliente, então o vendedor Chengdu desapareceu completamente porque essa regra foi removida. É aqui porque essa suposição está quebrada. Quando tentei fazer etapas subsequentes, ele não é capaz de transformar corretamente a tabela. E então aqui, está me dando. O erro real é, se você se lembrar do último passo que fizemos depois de promovermos os cabeçalhos, vimos oh, coluna do cliente, coluna de vendedor. Então, removemos a coluna. O código de programação subjacente real que o Power Query você está escrevendo é este. Eu diria que é o cabeçalho que tem um ponto e vírgula de vendedor ou dois pontos ou o que quer que seja, e depois torná-lo vendedor, cliente. Cólon para o cliente. Ele foi treinado para procurar uma coluna chamada vendedor, que nem está lá por causa da forma como os dados estão acontecendo agora. Por exemplo, se eu não me incomodar com a renomeação, ainda assim, minha consulta ainda funcionaria se eu excluir essa forma usando essa marca X, ela ainda funcionará. Então, neste momento, meus dados se parecem com isso. Mas se quisermos nomes de coluna corretos, então renomeamos, então isso vai quebrar. Portanto, essa é uma das coisas que você precisa ter em mente quando está trabalhando com o Power Query, essencialmente você está dizendo ajuda ao computador que não estava. Os computadores não são inteligentes. Eles apenas seguem as instruções. Então, como acontece com todos, depende de quão claras suas instruções. Nosso crédito é super inteligente, mas ainda é despejo. Não tem noção do que poderia quebrar. Então você teria que , como analista, antecipar o que quebraria e, em seguida, construir um processo repetitivo que funcione com isso. Você não precisa quebrar seu backend ou pensar tudo isso, mas depende da sua realidade. Sua realidade é que as regras podem desaparecer. Então você terá que construir um processo melhor ou você só tem que entrar no telefone e falar com o vendedor que está fazendo a forma de arte e, em seguida, dado um make fazê-lo entender ou dar ele um pedaço de sua mente para que eles possam, eles podem parar de excluir linhas ou qualquer coisa. Basta dizer que eles mantenham o modelo como está. Isso tornará nossa vida simples. Portanto, há maneiras diferentes de lidar com esse tipo de problema. Mas o que quer que esteja acontecendo, isso é algo que você precisa manter no fundo da sua mente, independentemente do que está acontecendo, isso ainda é muito melhor e muito mais rápido e inteligente do que fazer manualmente qualquer um desses trabalhos. Então é por isso que eu realmente gosto disso. Agora que entendemos o processo para um arquivo e uma parte do problema, nem pensamos em como obter as caixas e produtos reais e tudo mais. Então nós meio que entendemos para onde tudo está indo. Então, agora que vemos isso, agora vamos pegar isso e aplicá-lo para um arquivo grande, todo conjunto de arquivos em uma meta. Antes de fazermos isso, vou ler algumas perguntas para entender se há mais alguma coisa acontecendo. Chip pergunta Poderia se passos de dança que usamos? Sim, você pode adicionar regras e lógica extras e tudo o que diz se isso fizer isso, tudo isso é possível. Acho que não temos condições adicionadas na cobertura desta sessão. Mas eu tenho outro vídeo que falei um pouco mais sobre isso. Então, definitivamente, confira esse e você o encontrará. Sean diz que o motivo pelo qual você não manteve todos os dados seria apenas vendedor, nome do cliente em uma nova coluna. Sim. Isso é realmente o que vamos fazer. Mas não queríamos fazer isso apenas por um arquivo. Tivemos que fazer isso para todos os arquivos e depois combinar tudo. Então, em vez de lidar com todos os problemas nas duas vezes, na primeira vez, obtemos apenas o nome, nome do cliente e o nome do vendedor. Da próxima vez que vamos fazer tudo juntos. Você verá que estamos dizendo será usado para o Query Editor no Power BI para sim, você pode. O que quer que você esteja aprendendo, você não precisa usá-lo no Excel. Você pode usar as mesmas ideias no Power of Power BI. Então é por isso que acho que aprender Power Query é como o maior estrondo de dinheiro, porque não só você está economizando tempo, mas economiza tempo duas vezes, uma vez no Excel, uma vez no Power BI. Então, por que você não faria isso? Imagine que você sempre quis visitar a França ou a Itália ou um dos países exóticos para você. Mas o maior buraco para você usar o idioma como todos falam um idioma diferente do que você. Mas agora imagine o que acontece. Como se você pudesse ir para Paris, você pode ir para a Itália, morar lá. Todos falarão a mesma língua que você faz em casa. Isso é realmente o que isso é. Você pode usar as duas ferramentas e ambas falam o mesmo idioma. Por isso, é super simples. O que quer que você aprenda, literalmente sem alterações, é exatamente a mesma tela, as mesmas etapas. Você fará o mesmo no Power BI? Esta é uma boa, mas nosso trabalho não está feito. Estamos apenas aquecendo agora. Faremos isso para todos os arquivos em um único objetivo. Esta é minha consulta original. Eu queria fazer uma coisinha antes de precisarmos disso. Nós apenas voltamos aqui e adicionamos essa linha em branco novamente. Meio que restaure a sanidade onde nosso pedido se forma e atualize isso para que as coisas apareçam aqui. Sim, os nomes das colunas ainda estão tendo esse caractere extra no final. Vamos viver com isso por enquanto. Tudo bem, então vou para uma nova planilha e , em seguida, nem precisamos ir lá. E então, a partir dos dados desta vez, queremos fazer o mesmo processo, mas para todos os arquivos. E não apenas obtenha o nome e o cliente, mas toda a lista de pedidos aqui. Em seguida, continue esse processo para todos os arquivos que tenhamos uma tabela gigante aqui. É um lembrete rápido, novamente, esta é minha arte para fazendas. A pasta tem muitos, muitos formulários de pedidos, então os zeros 01 são apenas uma ponta de iceberg. Tenho diferentes formas de arte. Se eu abrir um formulário aleatório aqui, você verá que esse cara aqui, seus dados são um pouco mais longos. Eles tinham 14 dias de informação que tem forma de água. Um não tinha 14. Acho que só estava à altura disso. E eles pediram ainda mais produtos do que o original. O nome e a ordem desses produtos também são diferentes, então tudo está confuso. Esses são os grandes desafios. Precisamos meio que obtê-lo. Aqui vamos dizer que obtenha dados em vez de um único arquivo. Queríamos obter os dados de um dobrável. Os processos, o que acontece com um arquivo é semelhante. Mas agora, em vez de dizer obter os dados de um arquivo, você está dizendo que head é um monte de arquivos. Obtenha os dados onde quer que tudo esteja juntos. Isso é o que a pasta que faremos. Essa opção. Se você estiver usando uma versão realmente antiga do Excel, talvez não veja a opção de pasta, mas você ainda pode fazê-lo tecnicamente. Eu recomendaria provavelmente uma coisa mais fácil atualizar a lei Unix Paul query, em vez de tentar distorcê-la e fazê-lo, o funcionamento interno é o mesmo. Eu realmente não sei exatamente quando a pasta que nossos pacientes começaram a aparecer. A coisa da pasta está lá no Power BI desde versões muito antigas eu acredito, mas provavelmente o Excel. Não sei se alguém pode vir na janela de bate-papo. Se você não vir, diga da pasta e, em seguida, vá para minha pasta de outros formulários e abra novamente. Essa é a pasta onde eu quero meus dados. Então, isso mostrará uma listagem de todos os arquivos que ele Telefone. As colunas de conteúdo são binárias, o que significa bytes e bits e bytes. Basicamente, é isso que arquivos e computadores são. Ele diz que encontrei todos esses arquivos e OEM, ele só mostra como 20 ou 25 funções passadas ou algo assim. O que você quer fazer? Você quer combinar? Você quer carregar a transformação antiga? Você queria cancelar? Combine isso realmente o que queremos, mas simplesmente não queremos combinar como é porque sabemos que nossos dados não estão corretamente moldados. Precisamos combinar e transformar. Então esse é o primeiro passo. Combine e transforme. Isso é o que realmente estamos buscando. Você verá algumas opções semelhantes no Senhor também. Não seja diretamente Senhor, apenas diga combinar e transformar dados. Isso abrirá uma página. Se e quando ele aparecer, eu não disse que queria combinar arquivos. Agora pergunta, onde você quer combinar? Então aqui estamos agora construindo esse processo repetitivo. Essencialmente, você dirá ao Power Query o que você escolher em um arquivo, então ele vai, ele vai escolher os mesmos dados de todos os arquivos. Cada arquivo tem essa planilha si hoy . Isso é o que eu vejo. Então você quer esses dados? Isso é o que realmente perguntando aqui. Não há muita escolha, então vamos apenas dizer, sim, legal, tudo bem. Isso é o que eu quero. Então, clicamos em OK. Então, isso novamente piscará a tela algumas vezes. L, como um tubo de luz, e eventualmente ele virá aqui e boom, todos os seus dados são mesclados. Agora posso esconder meu rosto para que possamos ver os dados. Há muito lixo nesses dados. Ainda temos que limpá-lo, mas pelo menos está tudo lá. Se eu for aqui e Control Plus, você pode ver que isso basicamente tomou menino de zeros 0110106 assim, todos os dados são basicamente retirados e colocados um embaixo do outro. Imagine se alguém estiver basicamente costurando os arquivos, um arquivo para arquivar três assim. Eles realmente não prestariam atenção. Eles não estão fazendo nenhum trabalho nisso. Eles não estão alterando os dados. Eles apenas se anexam um após o outro. Isso é realmente o que o Power Query fez até agora por nós. Isso é bastante inútil, mas é um ótimo ponto de partida. Pelo menos eu não precisei lidar com 100 arquivos de dados diferentes. Tenho que lidar com um conjunto de dados ******. A partir daqui, é fácil para mim aplicar coisas ou fazer coisas. Olá, Ian diz que definir meu Excel está falhando. Se meu tipo de dados for diferente quando eu automatizo usando a pasta qualquer solução, não tenho certeza por que isso falharia. É um pouco complicado dissecar o problema ou descobrir de onde as coisas estão dando errado, a partir de apenas uma mensagem, pode haver várias razões pelas quais isso seria acidente ou as coisas podem quebrar. Começando por coisas simples, como memória insuficiente ou você está executando muitas coisas para coisas mais complicadas. Você provavelmente iria querer, isso é algo que tipo de universal se você puder entrar no Power Query e há um erro na última etapa é basicamente você só quer voltar atrás. Você continua um passo de cada vez até encontrar o passo em cada vez até encontrar que as coisas não são um erro. E, em seguida, o próximo passo é um editor. Uma vez que você olha para isso, é aí que o problema está no novo estoque para descobrir por que é o problema. Espero que isso tenha dado alguma ideia. Se você já sabe que em você é como, cara, isso não está me ajudando, então eu me sinto à vontade para adicionar mais detalhes. Mas se não estiver diretamente conectado ao tópico em questão, eu não conseguiria ajudá-lo. Enquanto ele compartilha. dados de origem podem ser alterados sempre? Sim, esse é o ponto inteiro. Você pode ter dados de origem diferentes. Como no meu caso, eu queria alterar vários arquivos em uma pasta de mês diferente. Então, mesmo que eu esteja mostrando o processo para apenas um arquivo, uma pasta, e a ideia pode ser estendida. Ele fica um pouco mais técnico. Então, eu não queria estender essa masterclass por três horas e depois mostrar como fazer a coisa de várias pastas também. Mas uma vez que você sabe que esse é o processo a ser feito para uma pasta, então você só precisa pensar, se eu tiver seis pastas, então é muito fácil para você também. Obtenha a resposta para essa pergunta. Então, deixe-nos a técnica e seguida, chegar até tarde para várias florestas, provavelmente há vídeos ou tutoriais do YouTube na web que lhe darão peças perdidas desse quebra-cabeça. Pooja diz que a opção Pasta não está disponível para usuários de Mac. Bem, o que posso dizer? Estou até surpreso que algumas das coisas que estão disponíveis, porque só vi recentemente que eles estão adicionando suporte ao Power Query ao max. Pode ser um V enquanto foi antes de começar a ver o suporte de pasta em mente. A única coisa que posso dizer é que minhas simpatias são com você que o sal tem muito obrigado por compartilhar Ken Parker, lidar com diferentes cabeçalhos. Ele pode lidar com diferentes cabeçalhos. Você só precisa saber a técnica que tudo seja possível. Mas teremos que pensar, é basicamente como se você estivesse jogando xadrez com a criação de barras. Cada passo que você faz. Você tem que antecipar o que o Power Query fará se não for esse o caso. Então, se eu sou um Zoom como se eu estivesse renomeando uma coluna do vendedor também, Diretor de Vendas, então eu estou assumindo inerentemente, mesmo que não estejamos explicitamente dizendo isso, que haverá uma coluna chamada vendedor e, em seguida, ele deve ser renomeado para Diretor de Vendas. Mas e se não houver coluna de vendedor? Nós vimos isso antes. Então você teria que jogar o baú dois níveis de profundidade começando com uma suposição de que você deseja renomear a segunda coluna em vez de coluna do vendedor. É assim que você começa a pensar. E se não for o segundo? E se for o sétimo, então você terá que achar que tudo é possível. Você verá uma estrutura de programação. Isso é algo que eu faço pelo meu Eu sou transmissões ao vivo. Você pode ver que tenho meu discurso espirituoso no quadro aqui. Power Query é a AMI, jogo de palavras deliberado lá. Mas, essencialmente, a linguagem M. M é o nome do idioma que a consulta de energia usa. M idioma, ies. Que par os usuários de crédito construir de acordo com a infraestrutura por trás. E você pode escrever seu próprio código de idioma M se você ficar um pouco confortável. Não sei que muita coisa em Miami é bastante drástica, mas posso entender isso. Com isso. Como eu disse, isso é como alguém costurar, os arquivos. É muito útil. Eu realmente não posso fazer nada significativo com isso, mas é um problema melhor do que 100 problemas individuais, como um grande problema. Agora vamos entender o que realmente aconteceu nos bastidores porque é aí que a pista está. Se eu expandir as consultas, repente vejo um monte de consultas em vez de uma única consulta. Eu pensei, você sabe, nós simplesmente não tivemos uma conexão. Deve haver apenas uma consulta. Mas internamente, o Power Query agora organizará um monte de coisas diferentes para facilitar o mecanismo. Aqui você pode ver que há um monte de coisas aqui. Isso explicando, isso demora um pouco. Tipo, você sabe, se você estiver na mesma sala, então é mais fácil de explicar. Mas como estamos todos em salas diferentes em diferentes partes do mundo, vou tentar o meu melhor. Acho que é aqui que meu rosto provavelmente ajudará. Você pode ver minhas expressões também. Aqui temos pastas diferentes. O principal é que há um arquivo de transformação de efeitos. O que é isso, esta é a função que o Power Query aplicará em cada um dos arquivos na pasta que seu arquivo ofereceu tem 20 arquivos ou 40 arquivos em cada arquivo. Ele se aplicará. Funciona, a função é chamada de arquivo de transformação. E transformar arquivo de amostra é a consulta na qual essa função se baseia. Uma maneira de pensar é dentro disso, esse cara aqui. Dentro dessa amostra de transformação, digo ao Power Query o que eu queria fazer em um único arquivo. Assim que eu terminar de fazer isso, o Power Query construirá a função para mim, transformará a função amostrada. É uma função de linguagem M. Não precisamos saber nenhuma das coisas técnicas para que essa coisa funcione. Mas se eu quisesse mudá-lo, então precisamos saber. Se você está feliz com a configuração da terra agora e você vai dizer, ok, meu trabalho está feito. Não preciso de nada mais do que meus dados são combinados. Você nem precisa saber o que mais existe. Como nossos dados ainda estão confusos, precisamos saber o que está acontecendo. Transform sample é a função e o arquivo de amostra é minha consulta com base na qual a função é criada internamente. Se eu, por exemplo, alterar algo no arquivo de amostra, adicionar uma etapa extra ou fizer algo em um arquivo individual, essa função será atualizada e todos os arquivos na pasta, nós iremos tem nova função aplicada e dados mais recentes são gerados. Então, se eu não estiver satisfeito com a maneira como meus dados finais são, o lugar onde eu começaria mudar está na avaliação da amostra. Vamos ajustar a consulta de amostra até que nossa saída final seja adequada para nós. Isso é realmente o que vamos fazer. Quando você vai para a amostra. A amostra é basicamente construída a partir de um arquivo. Dessa forma, você não está sobrecarregado, você não está olhando para todos os 100 conjuntos de dados. Basta olhar para um arquivo e depois dizer por consulta, o que você quer fazer. Isso é o que realmente fizemos no início da sessão. Analisamos um arquivo, construímos o processo para obter os bits de dados deles. Aqui neste arquivo, o que faremos é a maneira como nossos dados são, é meio que um pouco confuso. Então, por enquanto, você não precisa, 9. Não esqueça este vídeo!!!: Muito obrigado por assistir a essas lições do Power Query. Espero que você tenha gostado. Por favor, não se esqueça de deixar um comentário na plataforma Skillshare para que outros alunos em potencial também possam curtir este curso específico. Desejo-lhe o melhor e mais poder para você no seu futuro. Adeus.