Log In
Register
Home
August 20, 2020
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi,
Can you upload a sample source file with those multiple sheets?
August 20, 2020
Offline
This is the file
Thanks for the reply
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Javier,
Try this query:
(You should replace the path to the file with yours)
let
Source = Excel.Workbook(File.Contents("C:\Users\Catalin\Desktop\Copy 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"
August 20, 2020
Offline
Hi Catalin,
If I use your query and replace that path "C:\Users\Catalin\Desktop\Copy 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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Try Web instead of Files:
Source = Excel.Workbook(File.Contents("C:\Users\Catalin\Desktop\Copy 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),
1 Guest(s)
