Eu costumo contar muitas vezes a história do Bobi, o cão maravilha, aquele que faz sempre o que o dono lhe diz para fazer.
Tantas que até criei uma entrada só para a explicar melhor.
E para que serve esta analogia em forma de anedota?
Há sempre um preço por semana para cada produto e país.
Mas é sempre só um por semana? Sempre?
Sim, sempre, exceptuando quando muda a meio da semana e aí são dois.
Vamos então à situação.
Recebemos um ficheiro com um conjunto pequeno de dados.
Temos, para cada semana do ano, um preço para cada “chave”, neste caso fazemos de conta que a chave é o produto e país.
Até aqui tudo bem, mas depois lá vem o Bobi.
Temos a semana de 15 a 21 de janeiro e depois uma entrada com datas entre 17 e 21 de janeiro para a mesmo produto e país.
A questão aqui é que quando eu procurar pelo preço do produto num certo mercado para um certo dia… podem aparecer dois.
Isto pode ser resolvido de várias maneiras, umas mais rápidas, outras mais simples, outras com trabalho manual e outras bem feitas. Gosto muito destas últimas.
Precisamos então de alterar os intervalos para não existirem mais sobreposições.
Para o caso que acabei de referir, se temos um preço de 15 a 21 de janeiro e depois um preço de 17 a 21 de janeiro, preciso de alterar o primeiro intervalo para 15 a 16 de janeiro e manter o segundo de 17 a 21.
Por onde começar? Por descobrir onde temos intervalos sobrepostos.
Adicionamos, para facilitar, uma coluna nova com a diferença de dias entre o início e o fim do intervalo.
Selecionamos o “a”, depois o “de”, vamos a “Add Column”, “Date” e “Subtract Days”.
Não esquecer de adicionar 1 para termos os dias entre o início e o fim do intervalo, aproveitando também para mudar o nome da coluna para “Dias”
Agora vamos ao nosso amigo “List.Dates”
List.Dates([de], [Dias], Duration.From(1))Ficamos agora com uma coluna nova, a “DatasNoIntervalo”, que tem a lista de datas para cada intervalo.
A seguir? “Expand to New Rows” para as “abrir”.
Depois de mudarmos a coluna para data, ficamos com uma linha para cada dia do intervalo definido.
Agora fazemos o inverso. Selecionamos as colunas para um “Group By”
E adicionamos, para além do “Count Rows” que aparece por omissão, o “All Rows”
Temos agora uma coluna com o “count” de linhas para cada produto, país e data. Relembrando que apenas deveríamos ter 1 linha por cada “chave”, podemos inspecionar onde nos aparecem duas.
E lá está, para o produto A, país J e dia 19 de janeiro… pertence ao intervalo de 15 a 21 e ao intervalo de 17 a 21.
Passo seguinte, limitar aos “count” acima de 1
Para ficarmos com uma tabela com os que precisamos de tratar.
Agora vou criar duas colunas novas, uma com a data mais baixa do “de” e outra com a data mais alta do “de”. Relembro que as datas finais dos intervalos são sempre iguais, apenas as datas iniciais são diferentes.
Como o vamos fazer? Comecemos pelo valor mais baixo. Criamos uma coluna nova e vamos buscar o valor mais baixo da coluna “de” que está dentro da tabela na coluna “All”.
List.Min([All][de])Fazemos o mesmo para o valor mais alto.
List.Max([All][de])Ficamos então com as colunas criadas com os valores mais baixos e mais altos para cada intervalo a que cada dia pertence.
Para facilitar, vamos agora expandir o “de” e o “a”.
Precisamos agora de mudar o intervalo para a data anterior ao início do intervalo seguinte.
Para o caso que vemos nas duas primeiras linhas, o intervalo “de 15 a 21” tem de ser mudado para acabar na data anterior ao intervalo “de 17 a 21”, ou seja, tem de ficar “de 15 a 16”.
Como fazer? Comparamos se estamos no primeiro caso, em que a data inicial do intervalo é igual à data mínima e, se sim, mudamos a data final deste intervalo para a data anterior à data máxima. Vamos lá tratar disto.
if([de]=[Min_de]) then Date.AddDays([Max_de],-1) else [a]
Vemos então que, para o intervalo de “15 a 21” temos a nova data final do intervalo, o “Novo_a”, com a data de 16, para não se sobrepor ao intervalo de “17 a 21”.
Mais abaixo vemos outro caso, em que temos o intervalo de “28/05 a 03/06” com nova data final de 31/5, para não se sobrepor ao intervalo de “01/06 a 03/06”.
Com estes dados fazemos um novo group by, desta vez com o produto, país, de, a, Novo_a e apenas com a agregação por omissão.
Com esta tabela, a que chamei “Alterações Tabela de Preços”, posso agora pegar na tabela original, a “Tabela de Preços (Base)” e ir buscar a nova data de fim de intervalo, a “Novo_a”.
Para isto vou fazer um join com vários campos, uma coisa que muitos de nós não sabem ser possível e, por isso, abundam por aí chaves compostas por várias colunas. Não estou a dizer que não é uma solução válida, mas muitas vezes não é necessária.
Agora expandimos o “Novo_a”
Ficamos com algumas linhas preenchidas onde temos a nova data de fim de intervalo.
Coalesce entre a nova e a antiga data de fim de intervalo.
[Novo_a] ?? [a]Depois de limparmos as colunas que já não nos interessam e darmos nomes e tipos certos às que sobram, ficamos com o que pretendemos.
Parece que nos deu muito trabalho, mas ficou feito uma vez e agora é reutilizado para todas as vezes em que nos mudam os dados de base.
Mais importante ainda é que ficamos com uma tabela com as alterações que foram necessárias fazer para não termos sobreposições e que podemos depois utilizar para quando nos perguntarem
“em quantas situações tivemos alterações de preços a meio da semana?”
Mais um Bobi que se resolveu.
(Imagem de destaque do post de Volodymyr Hryshchenko no Unsplash)
