Friday, April 5, 2024

Power Query - Calculate Between RATE x HOURS

     //Carrega tab Faturamento 

    #"Added Custom" = Table.AddColumn(FAT_Faturamento_Table, "Referencia", each "01_Prio"),

    //Carrega tab Rate 

    Rate_Faturamento_Table = DIM_TaxWallet_csv_DF,


    // Merge entre os datasets com base na referência e data

    Merged_Table = Table.Join(#"Added Custom", "Referencia", Rate_Faturamento_Table, "Referencia"),

    // Ajusta tipos de datas

    #"Changed Type2" = Table.TransformColumnTypes(Merged_Table,{{"DataFinal", type text}, {"DataInicial", type text}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"DataInicial", type date}, {"DataFinal", type date}, {"dt_Aberto", type date}}),

    // Criar validação da Rate Between

    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Valid_DateRate", each if ([dt_Aberto] >= [DataInicial] and [dt_Aberto] <= [DataFinal]) then "Y" else "N"),

    // Considera apenas validação Y 

    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Valid_DateRate] = "Y")),

    // Então Calcula Rate x Horas

    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "VlrCostEstimadoTicket", each [ValorHora] * [VlrEst_Prio]),

Power BI APP - Delete or Unpublish

 UNPUBLISH DELETE