Inserir Linhas Tabela Power Query

[Eduardo da Costa Manente]

Bom dia amigo, tudo bem com vocês?, preciso de ajuda.

Tenho uma tabela em meu projeto que recebe os registros de metas cadastradas em um sistema de gestão de transporte, todo início do mês preciso lançar as metas de todos os veículos sempre no primeiro dia do mês, essa meta é a mesma para quase todos os meses do ano, isso é um trabalho enorme por ter vários veículos na empresa e o sistema não faz o lançamento em lote.

Como já tenho registro de 4 meses conforme exemplo na planilha em anexo, preciso  de uma rotina no Power Query que localize a última data de cada um dos registros e sempre que iniciar o mês a rotina acrescente mais uma linha para cada um dos veículos representado pelo seu código conforme figura abaixo.

Exemplo: Abaixo o veículo de código 9 e 11, quando iniciar o mês de outubro o Power Query repita a linha de dados modificando o mês da data e quando virar o ano ele modifique o ano também.

Importante: Antes de inserir essa linha com a nova data, ele precisa verificar se essa data já está lançada no sistema porque uma vez no ano essa meta é alterada no sistema por isso a regra.

Quem puder me ajudar a criar essa rotina no Power Query,  DEUS abençoe.

Obrigado

Eduardo

Análise_Meta.pbix Base_Meta.xlsx

[Alex Pereira]

Olá veja se isso te atende:

Caso não conseguir replicar me fale que replico em seu conjunto de dados atual

Segue o código de exemplo:

 

let
    // Obter a data atual
    DataAtual = Date.From(DateTime.LocalNow()),

    // Dados exemplo
    Fonte = Table.FromRecords({
        [Código Veículo=1, Data Cadastro=#date(2023, 8, 1), Código Cliente=101, Média Mínima=50, Média Desejável=55, Média Máxima=60, Código Motorista=11, Faturamento Desejável=1000, Segmento="A", Meta Viagem=100],
        [Código Veículo=2, Data Cadastro=#date(2023, 6, 1), Código Cliente=102, Média Mínima=70, Média Desejável=75, Média Máxima=80, Código Motorista=12, Faturamento Desejável=1500, Segmento="B", Meta Viagem=200]
        // ... adicione mais registros conforme necessário
    }),

    // Encontrar a última entrada para cada código de veículo
    UltimaEntradaPorVeiculo = Table.Group(Fonte, {"Código Veículo"}, {{"UltimaData", each List.Max([Data Cadastro]), type date}, {"UltimaEntrada", each _, type table}}),

    // Adicionar coluna com a lista das primeiras datas dos meses até a data atual para cada veículo
    AdicionarProximasDatas = Table.AddColumn(UltimaEntradaPorVeiculo, "ProximasDatas", each 
        let
            UltimaData = [UltimaEntrada][Data Cadastro]{0},
            GerarDatas = List.Skip(List.Generate(
                () => UltimaData,
                each _ <= DataAtual,
                each Date.AddMonths(_, 1)
            ), 1)
        in
            GerarDatas
    , type list),

    // Expandir as datas na coluna ProximasDatas em linhas separadas
    ExpandirDatas = Table.ExpandListColumn(AdicionarProximasDatas, "ProximasDatas"),
    
    // Expandir os detalhes da última entrada em colunas
    ExpandirDetalhes = Table.ExpandTableColumn(ExpandirDatas, "UltimaEntrada", {"Código Cliente", "Média Mínima", "Média Desejável", "Média Máxima", "Código Motorista", "Faturamento Desejável", "Segmento", "Meta Viagem"}),

    // Adicionar novas linhas ao conjunto de dados original
    NovosDados = Table.Combine({Fonte, ExpandirDetalhes}),
    #"Coluna Condicional Adicionada" = Table.AddColumn(NovosDados, "Data Referência", each if [Data Cadastro] = null then [ProximasDatas] else [Data Cadastro]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Coluna Condicional Adicionada",{{"Código Veículo", Int64.Type}, {"Data Cadastro", type date}, {"Código Cliente", Int64.Type}, {"Média Mínima", Int64.Type}, {"Média Desejável", Int64.Type}, {"Média Máxima", Int64.Type}, {"Código Motorista", Int64.Type}, {"Faturamento Desejável", Int64.Type}, {"Segmento", type text}, {"Meta Viagem", Int64.Type}, {"UltimaData", type date}, {"ProximasDatas", type date}, {"Data Referência", type date}}),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Tipo Alterado",{"Código Veículo", "Data Cadastro", "Data Referência", "UltimaData", "ProximasDatas", "Código Cliente", "Média Mínima", "Média Desejável", "Média Máxima", "Código Motorista", "Faturamento Desejável", "Segmento", "Meta Viagem"}),
    #"Linhas Classificadas" = Table.Sort(#"Colunas Reordenadas",{{"Código Veículo", Order.Ascending}})
in
    #"Linhas Classificadas"

[Carlos Silva]

Boa noite Eduardo!
Tudo bem?
A parte de adicionar uma nova linha com os valores da meta é possível, porém fiquei em duvida nessa parte,
você quer criar uma integração entre o powerquery e o sistema da empresa, da forma que escreveu entendi isso?

Importante: Antes de inserir essa linha com a nova data, ele precisa verificar se essa data já está lançada no sistema porque uma vez no ano essa meta é alterada no sistema por isso a regra.

[Eduardo da Costa Manente]

Em 03/10/2023 em 07:16, Alex Pereira disse:

Bom dia Alex, tudo bem?

Esse código que você me enviou quando mudar o ano ele muda o dia, mês e ano também?

 

[Alex Pereira]

Na lógica que eu fiz ele pega a última data que teve cadastro para cada produto e prencha as demais datas até ano e mes atual. Suponhamos:

cod 04 data 01/07/2023 valor 7
cod 04 data 01/08/2023 valor 10

--- ele iria colocar as seguintes linhas novas

cod 04 data 01/09/2023 valor 10
cod 04 data 01/10/2023 valor 10

Desta forma ele pega os valores da coluna do última data se cdastrou e replica para os demais meses

[Eduardo da Costa Manente]

Boa noite Alex, obrigado pelo retorno.

Alex, a minha pergunta é, quando virar o ano ele vai inserir 01/01/2024?

Se não, o que é preciso fazer para isso ser automatizado?

[Alex Pereira]

17 horas atrás, Eduardo da Costa Manente disse:

 

Vai sim no entanto esse meu codigo leva em conta só preenche depois da última data preeenchida.

Vou disponabilizar o código aqui aplicado no seu conjunto de dados atual, verifique se é isso você deseja

Análise_Meta.pbix

[Eduardo da Costa Manente]

Bom dia Alex, tudo bem?

A solução fornecida por vc deu certo fiz pequenos ajuste a minha realidade no final do código abaixo.

Obrigado pela ajuda, DEUS abençoe.

Eduardo.

Fonte: acesso aos campos da tabela do bando de dados.

 //Obter a data atual
    DataAtual = Date.From(DateTime.LocalNow()),
    
    //Encontrar a última entrada para cada código de veículo
    UltimaEntradaPorVeiculo = Table.Group(Fonte,{"Código Veículo"},{{"UltimaData", each List.Max([Data Cadastro]),type date},{"UltimaEntrada", each _, type table}}),
    //Adicionar coluna a lista das primeiras datas dos meses até a data atual para cada veículo
    AdicionarProximasDatas = Table.AddColumn(UltimaEntradaPorVeiculo,"ProximasDatas", each
        let
        UltimaData = [UltimaEntrada][Data Cadastro]{0},
        GerarDatas = List.Skip(List.Generate(
            ()=> UltimaData,
            each _ <= DataAtual,
            each Date.AddMonths(_, 1)
        ), 1)
        in
        GerarDatas, type list
    ),
    //Expandir as dastas na coluna ProximasDatas em linhas separadas
    ExpandirDatas = Table.ExpandListColumn(AdicionarProximasDatas, "ProximasDatas"),

    //Expandir os detalhes da última entrada em colunas
    ExpandirDetalhes = Table.ExpandTableColumn(ExpandirDatas,"UltimaEntrada",{"Código Cliente","Média Mínima","Média Desejável","Média Máxima","Código Motorista","Faturamento Desejável","Segmento","Meta Viagem" }),

    //Adicionar novas linhas ao conjunto de dados original
    NovosDados = Table.Combine({Fonte,ExpandirDetalhes}),
    
    //Coluna condicional adicionada
    ColunaCondicionalAdicionada = Table.AddColumn(NovosDados, "Data Referência", each if [Data Cadastro] = null then [ProximasDatas] else [Data Cadastro]),

    //Remover os registros duplicados com base no código do veículo e data de referencia.
    DulicatasRemovidas = Table.Distinct(ColunaCondicionalAdicionada, {"Código Veículo", "Data Referência"}),

    //Colunas que serão usadas
    ColunasUsadas = Table.SelectColumns(DulicatasRemovidas,{"Código Veículo", "Código Cliente", "Média Mínima", "Média Desejável", "Média Máxima", "Código Motorista", "Faturamento Desejável", "Meta Viagem", "Segmento","Data Referência"}),
    
    //Colunas Renomeadas
    ColunasRenomeadas = Table.RenameColumns(ColunasUsadas,{{"Data Referência", "Data Cadastro"}}),

    //Alterar o tipo de dados
    TipoAlterado = Table.TransformColumnTypes(ColunasRenomeadas,{{"Código Veículo", Int64.Type}, {"Código Cliente", Int64.Type}, {"Média Mínima", type number}, {"Média Desejável", type number}, {"Média Máxima", type number}, {"Código Motorista", Int64.Type}, {"Faturamento Desejável", Int64.Type}, {"Meta Viagem", Int64.Type}, {"Segmento", type text}, {"Data Cadastro", type date}})
in
    TipoAlterado