Power Query Excel - Tutorial Completo para Transformar Dados e Criar Tabelas Dinâmicas

Excel Tabela1 Editor de Consultas ArqIuvo Página Inicial Transformar Adicionar Colunas Exibição Fechar e Atualizar Consulta Propriedades Editor Avançado Escolher Remover Manter Remover Colunas Colunas Linhas Linhas Remover Duplicatas Remover Erros Dividir Agrupar Tipo de Dados Texto Usar Primeira Linha como Cabeçalho Substituir Valores Mesclar Consultas Acrescentar Consultas Combinar Binários Nova Fonte Fontes Recentes Fechar Consulta TableTransformColumnTypesFonteCustomer type text Product type text January Int64Type February type any March Customer Product January February March ABC Discounts Farmhouse Bloomer 122 null 46 ABC Discounts Gluten Free null 55 null ABC Discounts White Sliced null 110 58 DTs DTs 174 null 115 DTs Farmhouse Bloomer null null 56 DTs Gluten Free null 131 46 DTs Tiger 129 350 206 DTs White Sliced 50 76 70 Dollar Stretcher Brown Sliced 328 38 null Dollar Stretcher Farmhouse Bloomer null 113 141 Dollar Stretcher Gluten Free null null 50 Dollar Stretcher Tiger 83 54 123 Dollar Stretcher White Sliced 75 39 50 Mega Mart Brown Sliced 49 53 186 Config Consulta PROPRIEDADES Nome Tabela1 Todas as propriedades ETAPAS APLICADAS Fonte Tipo Alterado Uso de Estrura de Dados Correta Previne Lutas contra o Excel Fórmulas avançadas e complexas Criação de colunas de apoio sem necessidade Duplicação de Dados Objetivo Dimensões Fato Customer Produto Mês Unidades Vendidas Selecionar uma célula da Tabela e pressionar CTRLT Criar uma Tabela que pode ser renomeada para Vendas Ativar o Power Query através da alternativa Da Tabela Tabela no Formato Final Unpivot Transformar Colunas em Linhas Selecionar as colunas Customer e Product e Transformar Outras Colunas em Linhas Agora temos a Tabelas no formato Dimensões Fatos Para deixar mais claro ajustar os rótulos para retratar a informação armazenada Estrutura de Dados Criar um Range Conjunto de Células com um Nome Iniciar o Power Query carregando o Range Excel Range Editor de Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Propriedades Editor Avançado Remover Duplicatas Remover Erros Tipo de Dados Qualquer Usar Primeira Linha como Cabeçalho Substituir Valores Mesclar Consultas Acrescentar Consultas Combinar Binários Nova Fonte Fontes Recentes Fechar e Carregar Atualizar Visualização Escolher Remover Manter Remover Colunas Colunas Linhas Linhas Dividir Agrupar Coluna por Gerenciar Colunas Reduzir Linhas Classificar Transformar Combinar Nova Consulta Config Consulta PROPRIEDADES Nome Range Todas as propriedades ETAPAS APLICADAS Fonte Tipo Alterado Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 1 null null January null February null March null 2 Customer Product Value Units Value Units Value Units 3 ABC Discounts Farmhouse Bloomer 122 101 null null 46 38 4 ABC Discounts Gluten Free null null 55 50 null null 5 ABC Discounts White Sliced null null 110 122 58 64 6 DTs Brown Sliced 174 193 null null 115 128 7 DTs Farmhouse Bloomer null null null null 56 47 8 DTs Gluten Free null null 131 119 46 42 Transpondo a Tabela Excel Range Editor de Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Transpor Inverter Linhas Contagem de Linhas Agrupar Usar Primeira Linha por como Cabeçalho Tabela Tipo de Dados Qualquer Preenchimento Substituir Valores Substituir Erros Renomear Coluna Dinâmica Transformar Colunas em Linhas Mover Qualquer Coluna Dividir Coluna Formato Coluna de Texto Analisar Estatísticas Padrão Científico Trigonometria Arredondamento Informações Data Hora Duração Coluna Estruturada Config Consulta PROPRIEDADES Nome Range Todas as propriedades ETAPAS APLICADAS Fonte Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 1 null null January null February null March null 2 Customer Product Value Units Value Units Value Units 3 ABC Discounts Farmhouse Bloomer 122 101 null null 46 38 4 ABC Discounts Gluten Free null null 55 50 null null 5 ABC Discounts White Sliced null null 110 122 58 64 6 DTs Brown Sliced 174 193 null null 115 128 7 DTs Farmhouse Bloomer null null null null 56 47 8 DTs Gluten Free null null 131 119 46 42 Excel Range Editor de Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Agrupar Usar Primeira Linha por como Cabeçalho Tabela Transpor Inverter Linhas Contagem de Linhas Tipo de Dados Qualquer Preenchimento Substituir Valores Substituir Erros Renomear Coluna Dinâmica Transformar Colunas em Linhas Mover Qualquer Coluna Dividir Coluna Formato Coluna de Texto Analisar Estatísticas Padrão Científico Trigonometria Arredondamento Informações Data Hora Duração Coluna Estruturada Config Consulta PROPRIEDADES Nome Range Todas as propriedades ETAPAS APLICADAS Fonte Tabela Transposta Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 1 null Customer ABC Discounts ABC Discounts ABC Discounts DTs DTs DTs DTs DTs DTs 2 null Product Farmhouse Blo Gluten Free White Sliced Brown Sliced Farmhouse Blo Gluten Free Tiger White Sliced Brown S 3 January Value 122 null null 174 null null null 129 50 4 null Units 101 null null 193 null null null 99 56 5 February Value null 55 110 null null 131 350 76 6 null Units null 50 122 null null 119 269 84 7 March Value 46 null 58 115 56 46 206 70 8 null Units 38 null 64 128 47 42 158 78 TableTransposeFonte TableFillDownTabela TranspostaColumn1 Realizar uma Fusão das Duas Primeiras Colunas TableTransposeColunas Mescladas Remover o Espaço Antes de Column1 e Column2 Promover os Cabeçalhos Range Editor de Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Transpor Inverter Linhas Contagem de Linhas Tipo de Dados Texto Substituir Valores Substituir Erros Preenchimento Coluna Dinâmica Transformar Colunas em Linhas Renomear Mover Dividir Coluna Formato Analisar Mesclar Colunas Comprimento Estatísticas Padrão Científico Trigonometria Arredondamento Informações Data Hora Duração Coluna Estruturada Config Consulta PROPRIEDADES Nome Range Todas as propriedades ETAPAS APLICADAS Fonte Tabela Transposta Preenchido Abaixo Colunas Mescladas Tabela Transposta1 Valor Substituído Cabeçalhos promovidos TablePromoteHeadersValor Substituído Customer Product January Value January Units February Value February Units March Value March Units 1 ABCDiscounts FarmhouseBloomer 122 101 null null 46 38 2 ABCDiscounts GlutenFree null null 55 50 null null 3 ABCDiscounts WhiteSliced null null 110 122 58 64 4 DTs BrownSliced 174 193 null null 115 128 5 DTs FarmhouseBloomer null null null null 56 47 6 DTs GlutenFree null null 131 119 46 42 7 DTs Tiger 129 99 350 269 206 158 8 DTs WhiteSliced 50 56 76 84 70 78 9 DollarStretcher BrownSliced 328 363 38 42 null null 10 DollarStretcher FarmhouseBloomer null null 113 94 141 117 11 DollarStretcher GlutenFree null null null null 50 45 12 DollarStretcher Tiger 83 64 54 42 123 94 Transformar Colunas em Linhas Range Editor de Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Transpor Inverter Linhas Contagem de Linhas Tipo de Dados Texto Substituir Valores Substituir Erros Preenchimento Coluna Dinâmica Transformar Colunas em Linhas Renomear Mover Dividir Coluna Formato Analisar Mesclar Colunas Comprimento Estatísticas Padrão Científico Trigonometria Arredondamento Informações Data Hora Duração Coluna Estruturada Config Consulta PROPRIEDADES Nome Range Todas as propriedades ETAPAS APLICADAS Fonte Tabela Transposta Preenchido Abaixo Colunas Mescladas Tabela Transposta1 Valor Substituído Cabeçalhos promovidos Outras Colunas Não Dinâmicas TableUnpivotOtherColumnsCabeçalhos promovidos Customer Product Atributo Valor Customer Product Atributo Valor 1 ABCDiscounts FarmhouseBloomer January Value 122 2 ABCDiscounts FarmhouseBloomer January Units 101 3 ABCDiscounts FarmhouseBloomer March Value 46 4 ABCDiscounts FarmhouseBloomer March Units 38 5 ABCDiscounts GlutenFree February Value 55 6 ABCDiscounts GlutenFree February Units 50 7 ABCDiscounts WhiteSliced February Value 110 8 ABCDiscounts WhiteSliced February Units 122 9 ABCDiscounts WhiteSliced March Value 58 10 ABCDiscounts WhiteSliced March Units 64 11 DTs BrownSliced January Value 174 12 DTs BrownSliced January Units 193 13 DTs BrownSliced March Value 115 Dividir Coluna Pelo Delimitador Range Editor de Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Transpor Inverter Linhas Contagem de Linhas Tipo de Dados Texto Substituir Valores Substituir Erros Preenchimento Coluna Dinâmica Transformar Colunas em Linhas Renomear Mover Dividir Coluna Formato Analisar Mesclar Colunas Comprimento Estatísticas Padrão Científico Trigonometria Arredondamento Informações Data Hora Duração Coluna Estruturada Config Consulta PROPRIEDADES Nome Range Todas as propriedades ETAPAS APLICADAS Fonte Tabela Transposta Preenchido Abaixo Colunas Mescladas Tabela Transposta1 Valor Substituído Cabeçalhos promovidos Outras Colunas Não Dinâmicas Dividir Coluna pelo Delimitador TableSplitColumnOutras Colunas Não DinâmicasAtributoSplitterSplitTextByDelimiter Atributo1 Atributo2 Customer Product Atributo1 Atributo2 Valor 1 ABCDiscounts FarmhouseBloomer January Value 122 2 ABCDiscounts FarmhouseBloomer January Units 101 3 ABCDiscounts FarmhouseBloomer March Value 46 4 ABCDiscounts FarmhouseBloomer March Units 38 5 ABCDiscounts GlutenFree February Value 55 6 ABCDiscounts GlutenFree February Units 50 7 ABCDiscounts WhiteSliced February Value 110 8 ABCDiscounts WhiteSliced February Units 122 9 ABCDiscounts WhiteSliced March Value 58 10 ABCDiscounts WhiteSliced March Units 64 11 DTs BrownSliced January Value 174 12 DTs BrownSliced January Units 193 13 DTs BrownSliced March Value 115 14 DTs BrownSliced March Units 128 Coluna Dinâmica Use os nomes na coluna Atributo2 para criar novas colunas Coluna de valores Valor Opções avançadas Saiba mais sobre a Coluna Dinâmica OK Cancelar Pivot Coluna Dinâmica para Valor Renomear a Coluna Month Unpivot Pivot e Transposição Pivot Geralmente uma operação do tipo pivot significa virar ou rotacionar em torno de um ponto centra No Power Query Pivot é um passo de transformação que permite transformar um tabela através da rotação de suas colunas em linhas ou linhas em colunas e a respectiva agragação dos dados baseado nos valores dessas colunas Suponha que tenha um tabela como a mostrada abaixo Desejase uma tabela que mostre o total das vendas por produto agrupado por região O resultado da operação Pivot apresentará uma tabela como mostrada abaixo Coluna Dinâmica Coluna Dinâmica Use os nomes na coluna Produto para criar novas colunas Coluna de valores Região VendasUnidades OK Cancelar Pivot e Transposição Pivot e transposição são duas formas de rotacionar tabelas mas realizam de duas maneiras diferentes Pivot agrega os dados ao rotacionar já a transposição realiza a rotação sem alterar os dados Unpivot Unpivoting é o processo de transformação de dados baseados em colunas para dados baseados em linhas É o processo inverso do pivot discutido anteriormente Suponha que temos um tabela como essa Resultado da Operação Unpivot Product Attribute Value A Q1 Sales 100 A Q2 Sales 150 A Q3 Sales 200 A Q4 Sales 250 B Q1 Sales 75 B Q2 Sales 125 B Q3 Sales 175 B Q4 Sales 225 C Q1 Sales 50 C Q2 Sales 100 C Q3 Sales 150 C Q4 Sales 200 Arquivo Página Inicial Transformar Adicionar Colunas Exibição Agrupar Usar Primeira Linha Transpor Tipo de Dados Texto Preenchimento Renomear Mesclar Colunas Inverter Linhas Substituir Valores Coluna Dinâmica Mover Dividir Formato ABC Comprimento Estatísticas Padrão Científico Contagem de Linhas Substituir Erros Transformar Colunas em Linhas Coluna de Texto Coluna de Número Tabela Qualquer Coluna Produto Vendas Q1 Vendas Q2 Vendas Q3 Vendas Q4 1 A 100 150 150 200 2 B 75 125 175 175 3 C 50 100 150 150 Consultas Arquivo Página Inicial Transformar Adicionar Colunas Exibição Agrupar Usar Primeira Linha Transpor Tipo de Dados Número Inteiro Substituir Valores Mesclar Colunas Inverter Linhas Substituir Erros Dividir Formato Trigonometria Contagem de Linhas Comprimento Arredondamento Analisar Informações Estatísticas Padrão Científico Coluna de Número Tabela Qualquer Coluna Transformar Colunas em Linhas Transformar Outras Colunas em Linhas Produto Vendas Q1 Vendas Q2 Vendas Q3 Vendas Q4 1 A 100 150 150 200 2 B 75 125 175 175 3 C 50 100 150 150 Consultas Como podemos verificar a operação Unpivot alterou o formato dos dados de um baseado em coluna para um baseado em linhas A coluna com a informação relativa aos quartos tem o nome Atributo e as vendas correspondents estão na coluna Valor Essa tabela agora pode ser utilizada para examiner as informações de vendas por quarto e por produto Region Rep Date Binders Clipboards Pencils Pens Total East Ho 2Jan 86052 516 86568 East Ho 15Jan 20459 20459 East Ho 29Jan 45977 45977 East Ho 20Feb 4257 4257 East Ho 28Feb 12974 12974 East Jones 2Jan 5997 24451 5160 35608 East Jones 15Jan 9552 5669 15221 East Jones 29Jan 24458 24458 East Jones 20Feb 55972 55972 East Jones 28Feb 10978 10978 National Edwards 2Jan 165 19461 35961 National Edwards 20Feb 1393 1393 National Edwards 28Feb 1032 1032 National Johns 15Jan 15283 5489 20772 National Johns 29Jan 19778 5771 25549 National Johns 28Feb 19461 7485 26946 National Ryan 2Jan 11477 3781 8982 24240 National Ryan 15Jan 35982 1996 37978 National Ryan 29Jan 8982 8982 West Brunet 2Jan 5771 24451 30222 West Brunet 20Feb 995 995 West Brunet 28Feb 24451 12475 36926 West Gill 2Jan 31500 16467 13972 61939 West Gill 15Jan 89235 21457 110692 West Gill 29Jan 37758 18962 9751 66471 West Gill 28Feb 1161 Total 440034 166167 37014 160516 803731 TableTransformColumnTypesOutras Colunas Não DinâmicasDate type date Region Rep Date Atributo Valor East Ho 02012023 Binders 86052 East Ho 02012023 Pens 516 East Ho 15012023 Pens 20459 East Ho 29012023 Binders 45977 East Ho 20022023 Pens 4257 East Ho 28022023 Clipboards 12974 East Jones 02012023 Binders 5997 East Jones 02012023 Clipboards 24451 East Jones 02012023 Pens 5160 East Jones 15012023 Pencils 9552 East Jones 15012023 Pens 5669 East Jones 29012023 Pens 24458 East Jones 20022023 Binders 55972 East Jones 28022023 Pens 10978 National Edwards 02012023 Binders 165 National Edwards 02012023 Pens 19461 National Edwards 20022023 Pencils 1393 National Edwards 28022023 Pens 1032 National Johns 15012023 Binders 15283 National Johns 15012023 Clipboards 5489 National Johns 29012023 Binders 19778 National Johns 29012023 Pencils 5771 National Edwards 44977 1393 1393 National Edwards 44985 1032 1032 National Johns 44941 15283 5489 20772 National Johns 44955 19778 5771 25549 National Johns 44985 19461 7485 26946 National Ryan 44928 11477 3781 8982 2424 National Ryan 44941 35982 1996 37978 National Ryan 44955 8982 8982 West Brunet 44928 5771 24451 30222 West Brunet 44977 995 995 West Brunet 44985 24451 12475 36926 West Gill 44928 315 16467 West Gill 44941 89235 21457 West Gill 44955 37758 18962 44 1161 West Gill 44985 Total 440034 166167 Arquivo Página Inicial Inserir Layout da Página Fórmulas Dados Revisão Exibir Power Pivot Excel QM Mostrar Consultas Da Tabela Conexões Propriedades Atualizar Tudo Atualizar Links Texto para Colunas Obter Dados Externos Nova Consulta Fontes Recentes Obter e Transformar Conexões Classificar Filtro Limpar Reaplicar Avançado Classificar e Filtrar Ferramentas de Dados Region Rep Date Binders Clipboards Pencils Pens Total Region Rep Date Binders Clipboards Pencils Pens Total East Ho 44928 86052 516 86568 East Ho 44941 20459 20459 East Ho 44955 45977 45977 East Ho 44977 4257 4257 East Ho 44985 12974 12974 East Jones 44928 5997 24451 516 35608 East Jones 44941 9552 5669 15221 East Jones 44955 24458 24458 East Jones 44977 55972 55972 East Jones 44985 10978 10978 National Edwards 44928 165 19461 35961 National Edwards 44977 1393 1393 National Edwards 44985 1032 1032 National Johns 44941 15283 5489 20772 National Johns 44955 19778 5771 25549 National Johns 44985 19461 7485 26946 National Ryan 44928 11477 3781 8982 24240 National Ryan 44941 35982 1996 37978 National Ryan 44955 8982 8982 West Brunet 44928 5771 24451 30222 West Brunet 44977 995 995 West Brunet 44985 24451 12475 36926 West Gill 44928 315 16467 13972 61939 West Gill 44941 89235 21457 110692 West Gill 44955 37758 18962 9751 66471 West Gill 44985 1161 Total 440034 166167 37014 160516 803731 Remover Remover Outras Colunas Duplicar Coluna Remover Duplicatas Remover Erros Alterar Tipo Transformar Substituir Valores Substituir erros Agrupar por Preenchimento Colunas Não Dinâmicas Outras Colunas Não Dinâmicas Renomear Mover Busca Detalhada Adicionar como Nova Consulta Remover Linhas Remover Linhas Principais Remover Linhas Inferiores Remover linhas alternadas Remover Linhas em Branco Remover Linhas Inferiores Especifique quantas linhas serão removidas da parte inferior Número de linhas 1 OK Cancelar Arquivo Página Inicial Transformar Adicionar Colunas Exibição Tipo de Dados Qualquer Preenchimento Renomear Transpor Substituir Valores Coluna Dinâmica Mover Inverter Linhas Contagem de Linhas Substituir Erros Transformar Colunas em Linhas Dividir Formato Coluna Agrupar Usar Primeira Linha como Cabeçalho Transformar Colunas em Linhas Transformar Outras Colunas em Linhas Tabela Region Rep Date Binders Clipboards Pencils Pens 1 East Ho 44928 86052 null null 516 2 East Ho 44941 null null null 20459 3 East Ho 44955 45977 null null null 4 East Ho 44977 null null null 4257 5 East Ho 44985 null 12974 null null 6 East Jones 44928 5997 24451 null 516 7 East Jones 44941 null null 9552 5669 8 East Jones 44955 null null null 24458 9 East Jones 44977 55972 null null null 10 East Jones 44985 null null null 10978 11 National Edwards 44928 165 null null 19461 12 National Edwards 44977 null null 1393 null 13 National Edwards 44985 null null null 1032 14 National Johns 44941 15283 5489 null null 15 National Johns 44955 19778 null 5771 null 16 National Johns 44985 null 19461 null 7485 17 National Ryan 44928 null 11477 3781 8982 18 National Ryan 44941 35982 1996 null null 19 National Ryan 44955 null 8982 null null 20 West Brunet 44928 null null 5771 24451 21 West Brunet 44977 null null 995 null 22 West Brunet 44985 null 24451 null 12475 Arquivo Página Inicial Transformar Adicionar Colunas Exibição Tipo de Dados Texto Preenchimento Renomear Substituir Valores Coluna Dinâmica Mover Substituir Erros Transformar Colunas em Linhas Dividir Formato Coluna de Texto Agrupar Usar Primeira Linha como Cabeçalho Consultas TableUnpivotOtherColumnsLinhas Inferiores Removidas Region Rep Date Atributo Valor Region Rep Date Atributo Valor 1 East Ho 44928 Binders 2 East Ho 44928 Pens 3 East Ho 44941 Pens 4 East Ho 44955 Binders 5 East Ho 44977 Pens 6 East Ho 44985 Clipboards 7 East Jones 44928 Binders 8 East Jones 44928 Clipboards 9 East Jones 44928 Pens 10 East Jones 44941 Pencils 11 East Jones 44941 Pens 12 East Jones 44955 Pens 13 East Jones 44977 Binders 14 East Jones 44985 Pens 15 National Edwards 44928 Binders 16 National Edwards 44928 Pens 17 National Edwards 44977 Pencils 18 National Edwards 44985 Pens 19 National Johns 44941 Binders 20 National Johns 44941 Clipboards TableTransformColumnTypesOutras Colunas Não DinâmicasDate type date Region Rep Date Atributo Valor 1 East Ho 02012023 Binders 86052 2 East Ho 02012023 Pens 516 3 East Ho 15012023 Pens 20459 4 East Ho 29012023 Binders 45977 5 East Ho 20022023 Pens 4257 6 East Ho 28022023 Clipboards 12974 7 East Jones 02012023 Binders 5997 8 East Jones 02012023 Clipboards 24451 9 East Jones 02012023 Pens 516 10 East Jones 15012023 Pencils 9552 11 East Jones 15012023 Pens 5669 12 East Jones 29012023 Pens 24458 13 East Jones 20022023 Binders 55972 14 East Jones 28022023 Pens 10978 15 National Edwards 02012023 Binders 165 16 National Edwards 02012023 Pens 19461 17 National Edwards 20022023 Pencils 1393 18 National Edwards 28022023 Pens 1032 19 National Johns 15012023 Binders 15283 20 National Johns 15012023 Clipboards 5489 21 National Johns 29012023 Binders 19778 22 National Johns 29012023 Pencils 5771