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

(TextValue as nullable text) =>

let

    // If TextValue is null, replace it with an empty string

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

    // List of replacement pairs (accents and digits)

    AccentsList =

    {

        {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"},

        {"0",""},{"1",""},{"2",""},{"3",""},{"4",""},

        {"5",""},{"6",""},{"7",""},{"8",""},{"9",""}

    },

    // Convert the text into a list of characters, removing any null entries

    TextCharacters = List.RemoveNulls(Text.ToList(SafeText)),

    // Replace characters according to AccentsList and then combine back into a single string

    CleanedText = Text.Combine(List.ReplaceMatchingItems(TextCharacters, AccentsList), "")

in

    CleanedText

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