Preencher metas para meses sem lançamento de meta

[allisonresende]

Olá, estou com o seguinte caso, existe o lançamento para meta no sistema mas nem sempre era usado ou tem o lançamento em uma data mas preciso que seja replicado para outros meses para ter a granularidade de meta por mês/veículos.

Podemos ter algumas situações:

-O veiculo não ter nem uma meta, dai não tem o que fazer, colocar Meta para todos mês = 0
-O veiculo ter meta em um mês anterior, replicar para os meses subsequente que não tem meta
-O veiculo não tem meta anterior usar a meta posterior replicando para os meses anteriores sem metas

Com estaria os dado no Power Query:

Veiculo	Data	Meta
1	mar/22	45000
1	mai/22	40000
2	mar/22	50000
2	abr/22	45000
3	mai/22	0
4	jun/22	45000

 

Resultado desejado para período que o veiculo foi cadastrado ate a data atual:
 

Veiculo	Data	Meta	Meta atualiza
1	fev/22		45000
1	mar/22	45000	45000
1	abr/22		45000
1	mai/22	40000	40000
1	jun/22		40000
2	mar/22	50000	50000
2	abr/22	45000	45000
2	mai/22		45000
2	jun/22		45000
3	mai/22		0
3	jun/22		0
4	mai/22		45000
4	jun/22	45000	45000

 

Eu pensei em fazer no Power Query que que o Leo ensina sempre tentar fazer as correções no nível mais próximo a fonte de dados, no caso é uma consulta a um BD no select ja tento trazer somente uma linha por mês para cada veiculo.

Minha ideia e trazer no visual a meta tendo agrupamento por veiculo com os dados do jeito que estou falando daria para somar por total por veiculo ou por ano ou trimestre e taus e porde compara com o realizado conforme o mês/ano filtrado.

Meta Veiculos.xlsx

[Vitor Peralva]

Boa Tarde, @allisonresende!

Pensando uma forma fácil de fazer para você:

1) Imagino que originalmente a tabela viesse assim:

2) Vamos criar uma etapa para extrair a data máxima de toda a base:

Clique no fx na barra de fórmula e vai se criar uma etapa mais ou menos assim:

Obs.: O meu ficou Colunas Removidas, porque exclui a sua coluna de Meta Atualizada.

Altere a barra de fórmula para:

Para a sua base de exemplo, 01/06/2022 será retornado.

Renomeie o nome da estapa para DataMax.
3) Recuperar a consulta anterior.

Clique novamente no fx e na barra de fórmulas coloque = #"Colunas Removidas" (Vai ser o nome da etapa anterior a DataMax).

Voltará a ser exibida a tabela anterior.

Renomeie esta etapa para Base.

4) Vá na Guia Transformar e selecione Agrupar Por.

Preencha da seguinte forma:

 

5) Insira uma coluna personalizada (NumMeses) com o seguinte código:

let 

DtMax = DataMax,
DtMin = [DataMin],
MesMin = Date.Month(DtMin),
MesMax = Date.Month(DtMax),
AnoMin = Date.Year(DtMin),
AnoMax = Date.Year(DtMax),
CompAnos = AnoMax = AnoMin,
DifAnos = AnoMax - AnoMin - 1,
TotalMeses = 
if CompAnos then MesMax - MesMin 
else (DifAnos * 12) + MesMax + (12 - MesMin)

in 

TotalMeses

 

Seu resultado deve estar assim:

 

6) Adicione uma nova coluna personalizada (Personalizar) com o seguinte código:

{0 .. [NumMeses]}

 

Foi criada uma lista começando de zero e indo até a quantidade de meses que decorreram até a data máxima.

Expanda a lista.

7) Adicione mais uma coluna personalizada (Data) com o seguinte código:

Date.AddMonths([DataMin], [Personalizar])

😎 Mantenha somente a coluna de Veículo e Data.

9) Mescle a Consulta com ela mesma, por veículo e por data:

 

10) Vá até a barra de fórmulas e substitua a segunda tabela, no meu caso, #"Outras Colunas Removidas" por Base.

 

Antes:

Depois:

 

11) Expanda somente a coluna de Meta.

 

12) Agrupe Por Veículo e peça todas as linhas:

 

 

13) Adicione a seguite coluna personalizada:

let 

PreencheBaixo = Table.FillDown([Linhas],{"Meta"}),
PreencheCima = Table.FillUp(PreencheBaixo,{"Meta"}),
EliminaNull = Table.ReplaceValue(PreencheCima,null,0,Replacer.ReplaceValue,{"Meta"})

in 

EliminaNull

 

Como resultado:

 

14) Elimine as demais colunas, deixando somente a Personalizar:

15) Expanda a coluna Personalizar:

16) Defina os tipos e acredito que tem o resultado esperado.

 

Colocando o meu código inteiro como ficou:

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\vitor\Downloads\49939684_MetaVeiculos.xlsx"), null, true),
    Plan1_Sheet = Fonte{[Item="Plan1",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(Plan1_Sheet, [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Veiculo ", Int64.Type}, {"Data", type date}, {"Meta", Int64.Type}, {"Meta atualiza", Int64.Type}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado",{"Meta atualiza"}),
    DataMax = List.Max(#"Colunas Removidas"[Data]),
    Base = #"Colunas Removidas",
    #"Linhas Agrupadas" = Table.Group(Base, {"Veiculo "}, {{"DataMin", each List.Min([Data]), type nullable date}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Agrupadas", "NumMeses", each let 

DtMax = DataMax,
DtMin = [DataMin],
MesMin = Date.Month(DtMin),
MesMax = Date.Month(DtMax),
AnoMin = Date.Year(DtMin),
AnoMax = Date.Year(DtMax),
CompAnos = AnoMax = AnoMin,
DifAnos = AnoMax - AnoMin - 1,
TotalMeses = 
if CompAnos then MesMax - MesMin 
else (DifAnos * 12) + MesMax + (12 - MesMin)

in 

TotalMeses),
    #"Personalização Adicionada1" = Table.AddColumn(#"Personalização Adicionada", "Personalizar", each {0 .. [NumMeses]}),
    #"Personalizar Expandido" = Table.ExpandListColumn(#"Personalização Adicionada1", "Personalizar"),
    #"Personalização Adicionada2" = Table.AddColumn(#"Personalizar Expandido", "Data", each Date.AddMonths([DataMin], [Personalizar])),
    #"Outras Colunas Removidas" = Table.SelectColumns(#"Personalização Adicionada2",{"Veiculo ", "Data"}),
    #"Consultas Mescladas" = Table.NestedJoin(#"Outras Colunas Removidas", {"Veiculo ", "Data"}, Base, {"Veiculo ", "Data"}, "Outras Colunas Removidas", JoinKind.LeftOuter),
    #"Outras Colunas Removidas Expandido" = Table.ExpandTableColumn(#"Consultas Mescladas", "Outras Colunas Removidas", {"Meta"}, {"Meta"}),
    #"Linhas Agrupadas1" = Table.Group(#"Outras Colunas Removidas Expandido", {"Veiculo "}, {{"Linhas", each _, type table [#"Veiculo "=nullable number, Data=date, Meta=nullable number]}}),
    #"Personalização Adicionada3" = Table.AddColumn(#"Linhas Agrupadas1", "Personalizar", each let 

PreencheBaixo = Table.FillDown([Linhas],{"Meta"}),
PreencheCima = Table.FillUp(PreencheBaixo,{"Meta"}),
EliminaNull = Table.ReplaceValue(PreencheCima,null,0,Replacer.ReplaceValue,{"Meta"})

in 

EliminaNull),
    #"Outras Colunas Removidas1" = Table.SelectColumns(#"Personalização Adicionada3",{"Personalizar"}),
    #"Personalizar Expandido1" = Table.ExpandTableColumn(#"Outras Colunas Removidas1", "Personalizar", {"Veiculo ", "Data", "Meta"}, {"Veiculo ", "Data", "Meta"})
in
    #"Personalizar Expandido1"

 

Espero que consiga ter ajudado a resolver.

 

 

[Joao Barbosa]

1 hora atrás, allisonresende disse:

Olá, estou com o seguinte caso, existe o lançamento para meta no sistema mas nem sempre era usado ou tem o lançamento em uma data mas preciso que seja replicado para outros meses para ter a granularidade de meta por mês/veículos.

Podemos ter algumas situações:

-O veiculo não ter nem uma meta, dai não tem o que fazer, colocar Meta para todos mês = 0
-O veiculo ter meta em um mês anterior, replicar para os meses subsequente que não tem meta
-O veiculo não tem meta anterior usar a meta posterior replicando para os meses anteriores sem metas

Com estaria os dado no Power Query:

Veiculo	Data	Meta
1	mar/22	45000
1	mai/22	40000
2	mar/22	50000
2	abr/22	45000
3	mai/22	0
4	jun/22	45000

 

Resultado desejado para período que o veiculo foi cadastrado ate a data atual:
 

Veiculo	Data	Meta	Meta atualiza
1	fev/22		45000
1	mar/22	45000	45000
1	abr/22		45000
1	mai/22	40000	40000
1	jun/22		40000
2	mar/22	50000	50000
2	abr/22	45000	45000
2	mai/22		45000
2	jun/22		45000
3	mai/22		0
3	jun/22		0
4	mai/22		45000
4	jun/22	45000	45000

 

Eu pensei em fazer no Power Query que que o Leo ensina sempre tentar fazer as correções no nível mais próximo a fonte de dados, no caso é uma consulta a um BD no select ja tento trazer somente uma linha por mês para cada veiculo.

Minha ideia e trazer no visual a meta tendo agrupamento por veiculo com os dados do jeito que estou falando daria para somar por total por veiculo ou por ano ou trimestre e taus e porde compara com o realizado conforme o mês/ano filtrado.

Meta Veiculos.xlsx 11 kB · 0 downloads

Cara Boa Tarde ao meu ver , você teria que estabelecer melhor os critérios , por exemplo :

em que situação devo usar cada um dos critérios citados por você , você tem alguma tabela aonde existam essa regras?

Caminhão x  não existe meta , não puxar nada .
Caminhão y puxar sempre a anterior e assim sucessivamente.

Os critérios que não ficaram claros para mim.

[allisonresende]

1 minuto atrás, Joao Barbosa disse:

Cara Boa Tarde ao meu ver , você teria que estabelecer melhor os critérios , por exemplo :

em que situação devo usar cada um dos critérios citados por você , você tem alguma tabela aonde existam essa regras?

Caminhão x  não existe meta , não puxar nada .
Caminhão y puxar sempre a anterior e assim sucessivamente.

Os critérios que não ficaram claros para mim.

Tem que validar o que foi puxado.

[allisonresende]

Outra coisa também seria preencher as as linha para todos os meses do meu modelos, para cada veiculo validando as sua data de cadastro.

Meu modelo pega dados a parti de 2020

Veiculo cadastrado  em janeiro de 2000. 
Porem esse só tem meta em março de 2022.

Tem que criar uma linha para cada mês dedes o inicio do modelo. 

Já um veiculo cadastrado em abril de 2021

Que tem meta somente em março de 2022.

Tem que criar uma linha para cada mês desde data do cadastro do veiculo.