Power Query - funcao limpa replace texto numero

//fnc_limpa_texto

(TextValue as nullable text) =>

let

    SafeText = if TextValue = null then "" else TextValue,

    DigitsOnly = Text.Combine(

        List.Select(Text.ToList(SafeText), each Text.Contains("0123456789", _))

    )

in

    DigitsOnly

    

//fnc_limpa_numeros

(TextValue as nullable text) =>

let

    SafeText = if TextValue = null then "" else TextValue,

    // Tabela com caracteres acentuados e suas substituições

    AccentsList = {

        {"à","a"},{"á","a"},{"â","a"},{"ã","a"},{"ä","a"},

        {"è","e"},{"é","e"},{"ê","e"},{"ë","e"},

        {"ì","i"},{"í","i"},{"î","i"},{"ï","i"},

        {"ò","o"},{"ó","o"},{"ô","o"},{"õ","o"},{"ö","o"},

        {"ù","u"},{"ú","u"},{"û","u"},{"ü","u"},

        {"À","A"},{"Á","A"},{"Â","A"},{"Ã","A"},{"Ä","A"},

        {"È","E"},{"É","E"},{"Ê","E"},{"Ë","E"},

        {"Ì","I"},{"Í","I"},{"Î","I"},{"Ï","I"},

        {"Ò","O"},{"Ó","O"},{"Ô","O"},{"Õ","O"},{"Ö","O"},

        {"Ù","U"},{"Ú","U"},{"Û","U"},{"Ü","U"},

        {"ç","c"},{"Ç","C"},{"ñ","n"},{"Ñ","N"}

    },

    // Primeiro remove os acentos explicitamente

    WithoutAccents = List.Accumulate(

        AccentsList, 

        SafeText, 

        (text, pair) => Text.Replace(text, pair{0}, pair{1})

    ),

    // Remove todos os caracteres não alfabéticos (pontuação, especiais, números)

    CleanedText = Text.Combine(

        List.Select(

            Text.ToList(WithoutAccents), 

            each Text.Contains("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", _)

        ), ""

    )

in

    CleanedText   

Power Query - fnc funcao Date

   #"Changed column type" = Table.TransformColumnTypes(#"Renamed columns2", {{"INÍCIO DO ANO LETIVO", Int64.Type}, {"INÍCIO DO RECESSO", Int64.Type}, {"FIM DO ANO LETIVO", Int64.Type}, {"FIM DO RECESSO", Int64.Type}}),

  #"Lowercased text" = Table.TransformColumns(#"Changed column type", {{"INÍCIO DO ANO LETIVO", each Date.AddDays(DataBase, Number.FromText(Text.From(_))), type date}}),

  #"Lowercased text 1" = Table.TransformColumns(#"Lowercased text", {{"INÍCIO DO RECESSO",  each Date.AddDays(DataBase, Number.FromText(Text.From(_))), type date}}),

  #"Lowercased text 2" = Table.TransformColumns(#"Lowercased text 1", {{"FIM DO ANO LETIVO",  each Date.AddDays(DataBase, Number.FromText(Text.From(_))), type date}}),

  #"Lowercased text 3" = Table.TransformColumns(#"Lowercased text 2", {{"FIM DO RECESSO",  each Date.AddDays(DataBase, Number.FromText(Text.From(_))), type date}})

in

  #"Lowercased text 3"

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:

???