Power BI APP - Delete or Unpublish

 UNPUBLISH

DELETE

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]),

Script para executar scripts Google Colab

 script.bat

@echo off

powershell -ExecutionPolicy Bypass -File "D:\script.ps1"

script.ps1

# Defina a URL do link

$url = "https://colab.research.google.com/drive/sdfdsfgghjdghjgh"

# Inicie o Google Chrome

Start-Process "chrome.exe" -ArgumentList $url -PassThru | Wait-Process

# Aguarde um momento para o Chrome abrir completamente

Start-Sleep -Seconds 22

# Envie o atalho de teclado Ctrl+F9

Add-Type -AssemblyName System.Windows.Forms

[System.Windows.Forms.SendKeys]::SendWait("^{F9}")

Power Query - Valid Is Number

 let

    origem = TabelaExemplo, // Fonte da tabela

    selecionarNumericos = Table.SelectRows(origem, each Value.Is(Value.FromText([ColunaValores]), type number)) // Filtrar apenas valores numéricos

in

    selecionarNumericos

Power Query & SQL - Grouping Max Value ( Group by Group)

 script Power Query:

let

Source = Table.Combine({FAT_SAP_Lista_3, FAT_SAP_Lista_4}),

Step1 = Table.Group(Source, {"CUSTOMER"}, {{"MaxDate", each List.Max([DATEFROM]), type datetime}}),

Step2 = Table.Join(Source, {"CUSTOMER", "DATEFROM"}, Step1, {"CUSTOMER", "MaxDate"}),

Step3 = Table.SelectColumns(Step2,{"Source", "SHIP_COND", "KNVAL", "DATEFROM", "DATETO", "/BIC/ZMATERIAL", "CUSTOMER", "KNART","SALES_GRP"})

in

Step3

==============================

script SQL New:

WITH Source AS ( SELECT * FROM ( SELECT * FROM FAT_SAP_Lista_3 UNION ALL SELECT * FROM FAT_SAP_Lista_4 ) AS combined ), Step1 AS ( SELECT CUSTOMER, MAX(DATEFROM) AS MaxDate FROM Source GROUP BY CUSTOMER ), Step2 AS ( SELECT s.* FROM Source s INNER JOIN Step1 ON s.CUSTOMER = Step1.CUSTOMER AND s.DATEFROM = Step1.MaxDate ), Step3 AS ( SELECT Source, SHIP_COND, KNVAL, DATEFROM, DATETO, /BIC/ZMATERIAL, CUSTOMER, KNART, SALES_GRP FROM Step2 ) SELECT * FROM Step3;

==============================

script SQL Old:

???

Tabular Editor Advanced - Convert Measures Filters (Medidas em Filtros)

 

Install Tabular Editor:

https://tabulareditor.com/

https://www.sqlbi.com/tools/tabular-editor/

https://github.com/TabularEditor/TabularEditor/releases/tag/2.24.1

1 - Go to Table, create New calculation Group
2 - Rename "Name"

3 - Create New Calculations Items (...)

4 - Config each New Calculation Item (Print)

5 - Save

Power Query - Valida Preenchimento em Colunas

 = Table.TransformColumns(#"Added Custom1", {

{ "1", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "2", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "3", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "4", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "5", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "6", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "7", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "8", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "9", each if _ <> "" then 1 else 0, Int64.Type}, 

{ "10", each if _ <> "" then 1 else 0, Int64.Type},

{ "11", each if _ <> "" then 1 else 0, Int64.Type},

{ "12", each if _ <> "" then 1 else 0, Int64.Type},

{ "13", each if _ <> "" then 1 else 0, Int64.Type},

{ "14", each if _ <> "" then 1 else 0, Int64.Type},

{ "15", each if _ <> "" then 1 else 0, Int64.Type},

{ "16", each if _ <> "" then 1 else 0, Int64.Type},

{ "17", each if _ <> "" then 1 else 0, Int64.Type},

{ "18", each if _ <> "" then 1 else 0, Int64.Type},

{ "19", each if _ <> "" then 1 else 0, Int64.Type},

{ "20", each if _ <> "" then 1 else 0, Int64.Type},

{ "21", each if _ <> "" then 1 else 0, Int64.Type},

{ "22", each if _ <> "" then 1 else 0, Int64.Type},

{ "23", each if _ <> "" then 1 else 0, Int64.Type},

{ "24", each if _ <> "" then 1 else 0, Int64.Type},

{ "25", each if _ <> "" then 1 else 0, Int64.Type},

{ "26", each if _ <> "" then 1 else 0, Int64.Type},

{ "27", each if _ <> "" then 1 else 0, Int64.Type},

{ "28", each if _ <> "" then 1 else 0, Int64.Type},

{ "29", each if _ <> "" then 1 else 0, Int64.Type},

{ "30", each if _ <> "" then 1 else 0, Int64.Type},

{ "31", each if _ <> "" then 1 else 0, Int64.Type},

{ "32", each if _ <> "" then 1 else 0, Int64.Type},

{ "33", each if _ <> "" then 1 else 0, Int64.Type},

{ "34", each if _ <> "" then 1 else 0, Int64.Type},

{ "35", each if _ <> "" then 1 else 0, Int64.Type},

{ "36", each if _ <> "" then 1 else 0, Int64.Type},

{ "37", each if _ <> "" then 1 else 0, Int64.Type},

{ "38", each if _ <> "" then 1 else 0, Int64.Type},

{ "39", each if _ <> "" then 1 else 0, Int64.Type},

{ "40", each if _ <> "" then 1 else 0, Int64.Type},

{ "41", each if _ <> "" then 1 else 0, Int64.Type},

{ "42", each if _ <> "" then 1 else 0, Int64.Type},

{ "43", each if _ <> "" then 1 else 0, Int64.Type},

{ "44", each if _ <> "" then 1 else 0, Int64.Type},

{ "45", each if _ <> "" then 1 else 0, Int64.Type},

{ "46", each if _ <> "" then 1 else 0, Int64.Type},

{ "47", each if _ <> "" then 1 else 0, Int64.Type},

{ "48", each if _ <> "" then 1 else 0, Int64.Type},

{ "49", each if _ <> "" then 1 else 0, Int64.Type},

{ "50", each if _ <> "" then 1 else 0, Int64.Type},

{ "51", each if _ <> "" then 1 else 0, Int64.Type},

{ "52", each if _ <> "" then 1 else 0, Int64.Type}

    })