Power Query - IF THEN ELSE

 let

    Source = PowerBI.Dataflows(null),

    #"Added Custom" = Table.AddColumn(#"Expanded DIM_Prem_Cambio", "Value_USD", each if [Currency] = "USD" then  [Value]   

else if [Currency] = "EUR" then ([Value] * [EUR])   

else if [Currency] = "BRL" then ([Value] * [BRL]) 

else if [Currency] = "CAD" then ([Value] * [CAD]) 

else if [Currency] = "CHF" then ([Value] * [CHF]) 

else if [Currency] = "GBP" then ([Value] * [GBP]) 

else if [Currency] = "KRW" then ([Value] * [KRW]) 

else if [Currency] = "MYR" then ([Value] * [MYR]) 

else if [Currency] = "RMB" then ([Value] * [RMB]) 

else if [Currency] = "VND" then ([Value] * [VND]) 

else if [Currency] = "INR" then ([Value] * [INR]) 

else if [Currency] = "JPY" then ([Value] * [JPY]) 

else if [Currency] = "CNY" then ([Value] * [CNY]) 

else if [Currency] = "SEK" then ([Value] * [SEK]) 

else 0),

in

    #"Added Custom"

DATAFLOW PPU x PRO

Workspace PPU - (USER PPU)

Resource - Create table_PPU (Enable load - OFF) 

Workspace PRO - (USER PPU)

Resource - Create table_PRO_Prep (load table_PPU) (Enable load - OFF)

Load table_PRO (table_PRO_Prep) (Enable load - ON)

Workspace PRO - (USER PRO)

Create PBIX(load table_PRO) (Enable load - ON)

Power Query - Replace Function letter fnc_replace

(Texto as text) =>
let
    ListAccents = 
 {
 {null,"."},
 {"à","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"},
 {"Ò","O"},
 {"Ó","O"},
 {"Ô","O"},
 {"Õ","O"},
 {"Ö","O"},
 {"Ù","U"},
 {"Ú","U"},
 {"Û","U"},
 {"Ü","U"},
 {"ç","c"},
 {"Ç","C"},
 {"ñ","n"},
 {"Ñ","N"}
 }
in
    Text.Combine(List.ReplaceMatchingItems(Text.ToList(Texto), ListAccents))

Power Query - Python - Load Data with (Param)

 let

    Source = Python.Execute("# In[0]:#(lf)import pandas as pd#(lf)import yfinance as yf#(lf)import investpy#(lf)#(lf)param="&Text.From(P_Y_N)&"#(lf)vdt_BGN = pd.to_datetime('01/01/2021')#(lf)vdt_END = pd.to_datetime('today') + pd.offsets.DateOffset(days=1)#(lf)print(""vdt_BGN:"",vdt_BGN)#(lf)print(""vdt_END:"",vdt_END)#(lf)#(lf)# In[1]:#(lf)df_Tickers = investpy.get_stocks(country='Brazil')#(lf)df_Tickers['AcaoFull'] = df_Tickers['symbol']+'.SA'#(lf)df_Tickers['AcaoName'] = df_Tickers['AcaoFull']+' - '+df_Tickers['name']#(lf)df_Tickers.sort_values(by=['symbol'], inplace=True, ascending=True)#(lf)df_Tickers.drop_duplicates('AcaoFull') #(lf)df_Tickers['IndexCol'] = range(1, len(df_Tickers) + 1)#(lf)df_Tickers#(lf)#(lf)# In[2]:#(lf)update_list = [""BRKM3"",""TWTR34"",""EBAY34"",""JNJB34""]#(lf)df_Tickers['APO'] = 1#(lf)df_Tickers.loc[df_Tickers['symbol'].isin(update_list), 'APO'] = 0#(lf)df_Tickers.sort_values(by=['APO'], inplace=True, ascending=True)#(lf)#(lf)if param == 0:#(lf)    df_Tickers = df_Tickers[df_Tickers['APO'] == 0]#(lf)    #(lf)df_Tickers#(lf)#(lf)# In[3]:#(lf)#datasetTEMP1 = pd.DataFrame()#(lf)#(lf)for num, t in enumerate(df_Tickers['AcaoFull'], start=1):#(lf)  try: #(lf)#    dataset_temp=  yf.Ticker(t).history(period=""1y"")#(lf)    dataset_temp=  yf.Ticker(t).history(start=vdt_BGN, end=vdt_END)#(lf)    dataset_temp['Acao']=t#(lf)    dataset_temp['Updated']=pd.to_datetime('today')#(lf)    #datasetTEMP1 = datasetTEMP1.append([dataset_temp], sort=False)#(lf)    print(""{} TickerList OK: {}"".format(num, t))#(lf)  except Exception as e:#(lf)    print('TickerList Nok {}'.format(t))#(lf)    print(getattr(e, 'message', str(e)))#(lf)    #(lf)dataset = pd.DataFrame() #(lf)dataset = dataset.append([dataset_temp], sort=False)#(lf)dataset = dataset.reset_index()    #(lf)dataset = dataset.set_index([dataset.index])#(lf)dataset = dataset.fillna(0)                  #(lf)dataset['Adj Close'] = dataset['Close']"),

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

    #"Changed Type2" = Table.TransformColumnTypes(dataset,{{"Date", type datetime}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", type number}, {"Dividends", type number}, {"Stock Splits", type number}, {"Adj Close", type number}, {"Updated", type datetime}}),

    #"Parsed Date" = Table.TransformColumns(#"Changed Type2",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),

    //  Old  Dataset

    StepJoin = Table.Combine({#"Parsed Date" , FAT_Tickers_TA_Old}),

    #"Merged Queries" = Table.NestedJoin(StepJoin, {"Acao"}, DIM_Tickers, {".AcaoFull"}, "DIM_Tickers", JoinKind.LeftOuter),

    #"Expanded DIM_Tickers" = Table.ExpandTableColumn(#"Merged Queries", "DIM_Tickers", {"IndexCol", ".AcaoFull"}, {"IndexCol", ".AcaoFull"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DIM_Tickers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", ".PK_Acao_Date", each Text.PadStart([IndexCol],3,"0") & Number.ToText(Date.Year([Date])) & Number.ToText(Date.Month([Date]),"00") & Number.ToText(Date.Day([Date]),"00")),

    #"Sorted Rows" = Table.Sort(#"Added Custom",{{".PK_Acao_Date", Order.Ascending}}),

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "IndexPK", 1, 1, Int64.Type),

    FAT_Tickers_TA1 = Table.Distinct(#"Added Index", {".PK_Acao_Date"})

in

    FAT_Tickers_TA1

Python - Task Scheduler

File name (script_python.bat) 

Code:

@echo off 

CALL C:\Users\apo1979\Anaconda3\Scripts\activate.bat PyPWBI

C:/Users/apo1979/Anaconda3/envs/PyPWBI/python.exe "d:/.APO_OneDrive/script_python.py" runserver

pause