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

Power Query - Acesso CRM Dynamics ODATA

Exemplo 1 :

 let

    Source = OData.Feed("https://priocrm.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc", null, [Implementation="2.0"]),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "ActivityPointerSet")),

    ActivityPointerSet_table = #"Filtered Rows"{[Name="ActivityPointerSet",Signature="table"]}[Data],

  #"Removed Other Columns" = Table.SelectColumns(ActivityPointerSet_table, {"CreatedOn", "Description", "ActivityId", "Subject", "ActivityTypeCode", "ModifiedOn", "ModifiedBy", "RegardingObjectId"}),

  #"Expanded regardingobjectid_lead" = Table.ExpandRecordColumn(#"Removed Other Columns", "RegardingObjectId", {"LeadId"}, {"LeadId"}),

  #"Expanded modifiedby" = Table.ExpandRecordColumn(#"Expanded regardingobjectid_lead", "ModifiedBy", {"Fullname"}, {"Fullname"}),

    #"Filtered Rows1" = Table.SelectRows(#"Expanded modifiedby", each [LeadId] <> null and [LeadId] <> "")

in

  #"Filtered Rows1"

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

/*

let

    Source = OData.Feed("https://priocrm.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc", null, [Implementation="2.0"]),

    Source = OData.Feed("https://priocrm.crm4.dynamics.com/api/data/v8.1/", null, [Implementation="2.0"]),

    Source = OData.Feed("https://priocrm.crm4.dynamics.com/api/data/v9.1/", null, [Implementation="2.0"]),

    Source = PowerBI.Dataflows(null),

    VworkspaceId = Source{[workspaceId="4534543543543543256-ef1f382aaf76"]}[Data],

    VdataflowId = VworkspaceId{[dataflowId="3453454359de2-11f4805d6615"]}[Data],

    FAT_Table = VdataflowId{[entity="FAT_CRM_DGEG_activitypointers"]}[Data]

in

    FAT_Table 

*/

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

OBS: Para aceder via ODATA , sera necessario ter acesso de leitura as entidades do Dynamics!!!!

Python - Script to measure block performance (Time = Tempo)

 #Script de Tempo Begin

from datetime import datetime

from dateutil.relativedelta import relativedelta

def diff(t_a, t_b):

    t_diff = relativedelta(t_b, t_a)  # later/end time comes first!

    return '{h}h {m}m {s}s'.format(h=t_diff.hours, m=t_diff.minutes, s=t_diff.seconds)

t_a = datetime.now()

 #Processo a medir

df = pd.read_sql_query(""" SELECT * from ErrorLog """ , cnxn)

#Script de Tempo End

t_b = datetime.now()

diff(t_a,t_b)

print("Time End: ",diff(t_a,t_b))