Greetings Ms. Mynda and registered members, and Happy New year to all.
I was wondering something regarding PowerQuery. Right now I wanted to try something out regarding FX rates. Our country's National Bank updates currencies every day usually after lunch (Link for file). If I follow that link and download the file, it will show me all the currencies. I just need 4 of them (USD,EUR, GBP, COP) , the date, and last column.
I'm using Power Query in order to automate this extremely boring task, but the only complication so far for my skills, is the fact that they upload daily this file using a new "sheet" inside the workbook with a different name from the previous ones.
Is there a way I could use PowerQuery more smoothly in order to see the 4 currencies while I just move across my desire dates to see ?
I´m attaching my disastrous file so anyone could see it and hopefully understand me.
Hi,
Can you upload a sample source file with those multiple sheets?
This is the file
Thanks for the reply
Hi Javier,
Try this query:
(You should replace the path to the file with yours)
let
Source = Excel.Workbook(File.Contents("C:UsersCatalinDesktopCopy of 2_1_2a21_smc.xls"), null, true),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.ToColumns([Data])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Errors", "Custom.1", each if [Custom]{0}{0}=null then [Custom]{6}{0} else [Custom]{5}{0}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Custom.1", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data])), Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data])))
in
#"Expanded Data"
Hi Catalin,
If I use your query and replace that path "C:UsersCatalinDesktopCopy of 2_1_2a21_smc.xls" with the web link or saving the file on my desktop it just brings me de currencies of January the 13th.
It does not work.
My aim is to extract data automatically from the web, given that this link would be always the same "http://www.bcv.org.ve/sites/default/files/EstadisticasGeneral/2_1_2a21_smc.xls"
Thanks for the help thou
Try Web instead of Files:
Source = Excel.Workbook(File.Contents("C:UsersCatalinDesktopCopy of 2_1_2a21_smc.xls"), null, true),
Source = Excel.Workbook(Web.Contents("http://www.bcv.org.ve/sites/default/files/EstadisticasGeneral/2_1_2a21_smc.xls"), null, true),
