Power Query & Python - Loader Data

 let

    Source = Python.Execute("import pandas as pd#(lf)import investpy#(lf)data = [['brazil','AstraZeneca PLC','AstraZeneca PLC','.'   , 'BRL'   ,'A1ZN34' ] ]#(lf)data = pd.DataFrame(data,columns=['country',  'name',  'full_name',  'isin',  'currency',  'symbol'])#(lf)df_stocks = investpy.get_stocks(country='Brazil')#(lf)df_stocks = df_stocks.append([data], sort=False)#(lf)dataset = df_stocks"),

    dataset1 = Source{[Name="dataset"]}[Value]

in

    dataset1

Python Code:

import pandas as pd

import investpy

data = [['brazil','AstraZeneca PLC','AstraZeneca PLC','.'   , 'BRL'   ,'A1ZN34' ] ]

data = pd.DataFrame(data,columns=['country',  'name',  'full_name',  'isin',  'currency',  'symbol'])

df_stocks = investpy.get_stocks(country='Brazil')

df_stocks = df_stocks.append([data], sort=False)

dataset = df_stocks

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

Power Query - Read/Loader SharePoint.Files CSV , XLSX , JSON, XML, MySQL, Parquet, Azure Datalake & Azure Synapse

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

CSV: 

let

  Source = SharePoint.Files("https://site", [ApiVersion = 15]),

  Step01 = Table.SelectRows(Source, each Text.StartsWith([Name], "Cadastro_Clientes_Textos" and [Extension] = ".CSV")),

  Step03 = Table.SelectColumns(Step01, {"Content"}),

  step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])),

  step05 = Table.RemoveColumns(step04, {"Content"}),

  step06 = Table.ExpandTableColumn(step05, "Cols", {"Column1", "Column2"}, {"CUSTOMER", "Nome_Cliente"})

in

  step06

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

/*let

  Source = SharePoint.Files("https://SHAREPOINT_XPTO/sites/SITE_XPTO", [ApiVersion = 15]),

  Step1 = Table.SelectRows(Source, each Text.Contains([Folder Path], "Folder_XPTO") and [Name] = "FILE.xlsx"),

  Step2 = Table.SelectColumns(Step1, {"Content"}),

  Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),

*/

let

  Source = SharePoint.Contents("https://SHAREPOINT_XPTO/SITE_XPTO", [ApiVersion = 15]),

  Navigation = Source{[Name = "cdgdoc"]}[Content],

  Step1 = Table.SelectRows(Navigation, each [Name] = "FILE_XPTO.xlsx"),

  Step2 = Table.SelectColumns(Step1, {"Content"}),

  Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),

  Step4 = Table.RemoveColumns(Step3, {"Content"}),

  Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),

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

XLSX (Professional): 

let

  Source = SharePoint.Files("https://site", [ApiVersion = 15]),

  Step1 = Table.SelectRows(Source, each Text.Contains([Folder Path], "dataset_JoinWebservice") and [Name] = "DGEG_PostosJoin.xlsx"),

  Step2 = Table.SelectColumns(Step1, {"Content"}),

  Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),

  Step4 = Table.RemoveColumns(Step3, {"Content"}),

  Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),

  Step6 = Table.SelectRows(Step5, each [Name] = "Postos"),

  Step7 = Table.RemoveColumns(Step6, {"Item", "Kind", "Hidden", "Name"}),

  Step8 = Table.ExpandTableColumn(Step7, "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),

in

  Step8

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

XLSX(Personal/Sharepoint): 

let

  Source = SharePoint.Files("https://TENANT.sharepoint.com/personal/USERS_PT/", [ApiVersion = 15]),

  Step1 = Table.SelectRows(Source, each ([Folder Path] = "https://TENANT.sharepoint.com/personal/USERS_PT/Documents/Desktop/APODesktop/datasets/")and Text.Contains([Name], "PostosSAP")),

  Step2 = Table.SelectColumns(Step1, {"Content"}),

  Step3 = Table.AddColumn(Step2, "Cols", each Excel.Workbook([Content])),

  Step4 = Table.RemoveColumns(Step3, {"Content"}),

  Step5 = Table.ExpandTableColumn(Step4, "Cols", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),

  Step6 = Table.SelectRows(Step5, each ([Kind] = "Sheet")),

  Step7 = Table.SelectColumns(Step6,{"Data"}),

  Step8 = Table.ExpandTableColumn(Step7 , "Data", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})

in

  Step8

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

JSON: 

let

Source = SharePoint.Files("https://Mysharepoint", [ApiVersion = 15]),

Step01 = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://Mysharepoint_SearchFolder/")),

Step02 = Table.SelectColumns(Step01, {"Content", "Name", "Extension", "Folder Path"}),

Step03 = Table.SelectRows(Step02, each [Extension] = ".json"),

Step04 = Table.SelectColumns(Step03, {"Content"}),

Step05 = Table.AddColumn(Step04, "Json", each Json.Document([Content], 65001)),

Step06 = Table.SelectColumns(Step05, {"Json"}),

Step07 = Table.ExpandListColumn(Step06, "Json")

in

Step07 

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

MySQL: 

let

    Source = MySQL.Database("timesheet.xxxxx.pt:3306", "DBFIN", [ReturnSingleDatabase=true]),

    ssimulta_ssdbfin_bancos = Source{[Schema="dbfin",Item="bancos"]}[Data],

    #"Renamed Columns" = Table.RenameColumns(ssimulta_ssdbfin_bancos,{{"Descritivo", "Banco"}})

in

    #"Renamed Columns"  

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

Azure Datalake: 

let

  Source = AzureStorage.DataLake("https://DATALAKE.dfs.core.windows.net/FOLDER/SUBFOLDERS"),

  Step01 = Table.SelectRows(Source, each ([Name] = "pXPTO.csv")),

  Step03 = Table.SelectColumns(Step01, {"Content"}),

  step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None])),

  step05 = Table.RemoveColumns(step04, {"Content"}),

    #"Expanded Cols" = Table.ExpandTableColumn(step05, "Cols", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Cols", [PromoteAllScalars=true])

in

    #"Promoted Headers"

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

Azure Synapse: 

let

  Source = Sql.Database("ResourceSynapse-ondemand.sql.azuresynapse.net", "DatalakeName", 

[CommandTimeout = #duration(0, 2, 0, 0), Query = "SELECT * 

FROM Vendas_Integradas.VIEW_XXXX 

WHERE CALDAY >= 20220101 AND CALDAY <= 20221231"])

in

  Source

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

Azure Storage BLOB Content

Acesso via DATALAKE BLOB:

let

  Source = AzureStorage.Blobs("https://DATALAKE.blob.core.windows.net/"),

  Navigation = Source{[Name = "pbicontainer"]}[Data],

  #"Filtered rows6" = Table.SelectRows(Navigation, each Text.StartsWith([Name], "FOLDER/")),

  Step01 = Table.SelectRows(#"Filtered rows6", each [Name] = "FOLDER/FILE.CSV"),

  Step03 = Table.SelectColumns(Step01, {"Content"}),

  step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=72, Encoding=1252, QuoteStyle=QuoteStyle.None])),

  step06 = Table.RemoveColumns(step04, {"Content"}),

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

Azure Storage DATALAKE Content

Acesso via DATALAKE:

let

  Source = AzureStorage.DataLake("https://DATALAKE.dfs.core.windows.net/pbicontainer/FOLDER/FILE.CSV"),

  Step03 = Table.SelectColumns(Source, {"Content"}),

  step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=72, Encoding=65001, QuoteStyle=QuoteStyle.None])),

  step06 = Table.RemoveColumns(step04, {"Content"}),

  

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

Azure Storage BLOB Parquet Content

let

  Source = AzureStorage.Blobs("XPTO_DATALAKE"),

  Step02 = Source{[Name = "XPTO_DATALAKE"]}[Data],

  Step03 = Table.SelectRows(Step02, each Text.StartsWith([Name], "XPTO_PATH_Parquet/")),

  Step04 = Table.SelectRows(Step03, each [Extension] = ".parquet"),

  Step05 = Table.SelectColumns(Step04, {"Content"}),

  Step06 = Table.AddColumn(Step05, "Cols", each Parquet.Document(([Content]))),

  Step07 = Table.RemoveColumns(Step06, {"Content"}),

in

  Step07

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

Azure Storage BLOBContents Parquet

let

  Source = Parquet.Document(

AzureStorage.BlobContents("https://DATALAKE_XPTO.blob.core.windows.net/CONTAINER_XPTO/PATH_XPTO/File.parquet"))

in

  Source  

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

DATAFLOWS Old x New (PowerBI.Dataflows x PowerPlatform.Dataflows)

 let

    Source = PowerBI.Dataflows(),

    workspaceId = Source{[workspaceId="WP_XPTO"]}[Data],

    dataflowId = workspaceId{[dataflowId="DF_XPTO"]}[Data],

    DIM_Table = dataflowId{[entity="DIM_XPTO"]}[Data],

in

    DIM_Table 

let

    Source = PowerPlatform.Dataflows(),

    Workspaces = Source{[Id="Workspaces"]}[Data],

    Workspaces1 = Workspaces{[workspaceId="WP_XPTO"]}[Data],

    DataflowId = Workspaces1{[dataflowId="DF_XPTO"]}[Data],

    DIM_Table = DataflowId{[entity="DIM_XPTO",version=""]}[Data],

in

    DIM_Table 

Power Query - Text.Pad / LPAD

    StepPAD = Table.ReplaceValue(DIM_SAP_Grupo_Preco_Clientes1, each[PRICE_GRP], each Text.PadStart(Text.From([PRICE_GRP]),2,"0"),Replacer.ReplaceValue,{"PRICE_GRP"})

Power Query - Converter CSV / XLSX

 XLSX:

  Step10 = Table.SelectRows(#"Filtered rows 1", each [Extension] = ".xlsx"),

  Step11 = Table.SelectColumns(Step10, {"Content", "Name"}),

  Step12 = Step11{0}[Content],

  Step13 = Excel.Workbook(Step12, null, true),

  Step14 = Table.SelectColumns(Step13, {"Name", "Data"}),

CSV:

  Step03 = Table.SelectColumns(Step01, {"Content"}),

  step04 = Table.AddColumn(Step03, "Cols", each Csv.Document(([Content]),[Delimiter=";", Columns=50, Encoding=65001, QuoteStyle=QuoteStyle.None])),

  step06 = Table.RemoveColumns(step04, {"Content"}),