Friday, November 20, 2020

Power Query - DATAFLOW Multiply Access Database

 let
  Source2 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja002\xpto002.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source3 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja003\xpto003.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source4 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja004\xpto004.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source5 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja005\xpto005.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source6 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja006\xpto006.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source7 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja007\xpto007.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source8 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja008\xpto008.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source10 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja010\xpto010.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Source12 = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\BckMDB\BCK\Loja012\xpto012.mdb"";Jet OLEDB:Database Password=""xpto"";"),
  Tabela_Cli2 = Source2{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli3 = Source3{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli4 = Source4{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli5 = Source5{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli6 = Source6{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli7 = Source7{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli8 = Source8{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli10 = Source10{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli12 = Source12{[Name = "Tabela_Cli", Kind = "Table"]}[Data],
  Tabela_Cli = Table.Combine({Tabela_Cli2, Tabela_Cli3, Tabela_Cli4, Tabela_Cli5, Tabela_Cli6, Tabela_Cli7, Tabela_Cli8, Tabela_Cli10, Tabela_Cli12}),
  #"Removed other columns" = Table.SelectColumns(Tabela_Cli, {"Codigo", "Nome", "Endereco_Logradouro", "Endereco_Numero", "Bairro", "Cidade", "Codigo_Cidade", "Cep", "Uf", "Telefone", "Fax", "Aniversario", "Rg", "Cic", "Contato", "Credito", "Saldo", "Limite_credito", "Data_ultima", "Data_cadastro", "Cidade_ent", "Codigo_Cidade_Ent", "dia_pgto", "Email", "Maior_atraso", "PessoaFisica", "SexoMasc"}),
  Add3 = Table.AddColumn(#"Removed other columns", "CodLoja", each if Text.Length(Number.ToText([Codigo]))=8
                                               then Text.Middle(Text.From([Codigo], "en-US"), 0, 2)                                                
                                               else Text.Middle(Text.From([Codigo], "en-US"), 0, 1)),
  #"Removed duplicates" = Table.Distinct(Add3, {"Codigo"}),
  #"Added Custom" = Table.AddColumn(#"Removed duplicates", "Data_Ref_Cadastro", each if [Data_ultima] <> null and [Data_cadastro] <> null then
    if [Data_cadastro] > [Data_ultima] then 
    [Data_cadastro] else 
    [Data_ultima]
    else 
    if [Data_cadastro] <> null then 
    [Data_cadastro] else 
  #"Changed column type" = Table.TransformColumnTypes(#"Added Custom", {{"Data_Ref_Cadastro", type datetime}}),
  #"Transform columns" = Table.TransformColumnTypes(#"Changed column type", {{"CodLoja", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"CodLoja", null}})
in
  #"Replace errors"
    Date.AddMonths(Date.From(DateTime.LocalNow()),-60)),

No comments:

Post a Comment

Power Query - funcao limpa replace texto numero

//fnc_limpa_texto (TextValue as nullable text) => let     SafeText = if TextValue = null then "" else TextValue,     DigitsOnly...