Esta semana tive uma situação em que preciso criar uma tabela de orçamento no Power Pivot. Tive a opção de fazer upload de um orçamento semanal como um único registro por loja e usando DAX para calcular orçamentos acumulados no ano, ou carregue um conjunto de registros (um para cada semana) em uma tabela de orçamento. Eu escolhi o último neste caso como me deu a oportunidade de testar novas habilidades no Power Query. Eu recriei o cenário abaixo.
Orçamentos semanais por loja
Eu tenho uma planilha que contém o orçamento por loja por semana. O orçamento é o mesmo para cada semana para cada loja para a próxima 15 semanas. A tabela de orçamento é semelhante a esta: fácil de inserir dados, mas não é ideal para Power Pivot. No meu exemplo de vida real, havia muito mais colunas para Contração, GP e algumas outras métricas na página.
Placas longas e estreitas são geralmente melhores para Power Pivot, então eu usei o Power Query para remodelar esta tabela para se parecer com o seguinte, usando uma transformação de coluna não dinâmica simples do Power Query.
Uma vez que esta tabela foi criada no Power Query (importe da planilha e depois transforme), liguei para a consulta “Despesas” e defina como “basta criar conexão”.
Isso o torna disponível para uso em outras consultas. sem armazenar os dados em qualquer lugar do Excel. Cada vez que se materializa’ uma consulta adicionando-a como uma tabela no Excel ou Power Pivot, leva espaço. Só faça isso se precisar da visão materializada da mesa.
Números de identificação da semana
Eu precisava definir o orçamento a cada semana para a próxima 15 semanas. Eu estava usando um calendário semanal neste exemplo (não é um calendário diário), então eu tinha o ID da semana abaixo no formato YYWW. Observe o salto não contíguo no final do ano civil em 2016.
Crie a lista de IDs da semana no Power Query
Esta foi minha primeira chance de aprender uma nova habilidade... Eu sabia que era possível criar uma lista nativa no Power Query em vez de importar a lista do Excel (eu li isso em algum lugar, provavelmente algum de Chris Webb) mas eu não tinha certeza de como. Pesquisei no Google e tentei List.Generate () mas estava errado. Mais tarde, um flashback: tudo que você precisava fazer era usar a seguinte sintaxe com chaves.
= {1..5}
O acima gera uma lista de números do 1 al 5. Com isso em mente, eu fiz o seguinte:
- Eu criei uma nova consulta executando as seguintes etapas do menu:
- Consulta de poder
- De outras fontes
- Consulta em branco
- Nome da consulta alterado para “Semanas”.
- Eu escrevi uma linha de código como esta na barra de fórmulas = {1545..1552,1601..1607} e me deu exatamente o que eu precisava. Eu não sabia que a sintaxe da vírgula funcionaria para criar intervalos não contíguos quando escrevi a fórmula, mas funcionou. Depois de obter alguma prática e experiência com um novo idioma como este, você começa a ver os padrões na sintaxe e obtém chutes livres como este. (Observação: se você não pode ver a barra de fórmula, ative-o no menu Exibir)
- tenha em conta que neste momento é “só” uma lista, não é uma mesa. Para converter a lista em uma tabela, Cliquei em Transform to Table, então eu dei um nome de coluna para a nova tabela.
- Também salvei a consulta como “basta criar conexão” para que a mesa não se materializasse, Acabei de criar as instruções sobre como criar a tabela em uma nova consulta.
Nesse ponto, fiquei muito feliz e impressionado com minhas novas habilidades., mas ainda havia problemas pela frente. O próximo passo foi a junção cruzada das tabelas.
Cruze as duas mesas
A tabela de semanas acima tem 15 linhas e a tabela de orçamento tem 10 filas. Quando eu digo “junção cruzada”, o que quero dizer é que preciso duplicar toda a tabela de orçamento para cada semana, adicionar coluna de semana na tabela de orçamento. Então eu preciso de algo como mostrado abaixo, mas para cada combinação possível. quer dizer, 15 x 10 = 150 linhas na minha nova mesa.
Rapidamente pesquisei no Google Cross Join Power Query e encontrei algumas referências a Full Outer Joins, mas isso não é o que eu precisava. Então eu tive uma ideia: talvez você possa escrever uma função fictícia para simular o processo Cross Join. Eu tentei isso e funcionou bem.
Editar 31/10/15
Depois de postar, Recebi algumas boas sugestões de Imke Feldman; parece que a função não é necessária; realmente faz sentido, mas eu não tinha percebido. Chris Webb também publicou uma abordagem mais eficaz (o que é importante se você tiver placas grandes), então aqui está a abordagem atualizada.
Adicione uma coluna personalizada para criar uma junção cruzada
- Abra a tabela Semanas e adicione uma nova coluna personalizada (mostrado abaixo). A fórmula para a nova coluna é simplesmente o nome da consulta de orçamento.
- Depois de clicar em OK, obter uma nova coluna “contém” toda a tabela Orçamento como um objeto em cada linha da tabela Semanas (como é mostrado a seguir).
- Tudo o que você precisava fazer era expandir a nova coluna clicando no botão expandir (Mostrado acima) para criar todas as combinações possíveis (mostrado abaixo)
Eu configurei os tipos de dados das colunas numéricas e carreguei a tabela diretamente em meu modelo de dados para que possa usá-la no Power Pivot.
Você também pode ler a sugestão de Chris Webb nos comentários abaixo)
