Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to write a query in power bi desktop and get data from this link:
http://www.xe.com/currencytables/?from=USD&date=2017-12-12
but my question is, how can i write a query that append new data from the link, based on the date of my windows or list of dates in a table and append to my table.
another thing is i want it add a column that is the current date or th list of dates in a table
Solved! Go to Solution.
Hi @Asamadi,
According to your description above, it seems to be that you want to get history data of a range of dates(i.e. from 2017-12-01 to 2017-12-12) from that web site, right?
If that is the case, you can firstly create a function named "GetData" to get data of a specific date from that site.
(date1 as date) as table => let Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date="&Date.ToText(date1,"yyyy-MM-dd"))), Data = Source{0}[Data], #"Changed Type" = Table.TransformColumnTypes(Data,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}}) in #"Changed Type"
Then use the formula below to create a new query to get all data from the date range which you can specify in the query.
let Source = List.Dates(#date(2017, 12, 1), 10, #duration(1, 0, 0, 0)), ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Renamed = Table.RenameColumns(ToTable,{{"Column1", "Date"}}), Added = Table.AddColumn(Renamed, "Custom", each GetData([Date])), #"Expanded Custom" = Table.ExpandTableColumn(Added, "Custom", {"Currency code ▲▼", "Currency name ▲▼", "Units per USD", "USD per Unit"}, {"Custom.Currency code ▲▼", "Custom.Currency name ▲▼", "Custom.Units per USD", "Custom.USD per Unit"}) in #"Expanded Custom"
Here is the sample pbix file for your reference.
Regards
Hi @Asamadi,
According to your description above, it seems to be that you want to get history data of a range of dates(i.e. from 2017-12-01 to 2017-12-12) from that web site, right?
If that is the case, you can firstly create a function named "GetData" to get data of a specific date from that site.
(date1 as date) as table => let Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date="&Date.ToText(date1,"yyyy-MM-dd"))), Data = Source{0}[Data], #"Changed Type" = Table.TransformColumnTypes(Data,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}}) in #"Changed Type"
Then use the formula below to create a new query to get all data from the date range which you can specify in the query.
let Source = List.Dates(#date(2017, 12, 1), 10, #duration(1, 0, 0, 0)), ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), Renamed = Table.RenameColumns(ToTable,{{"Column1", "Date"}}), Added = Table.AddColumn(Renamed, "Custom", each GetData([Date])), #"Expanded Custom" = Table.ExpandTableColumn(Added, "Custom", {"Currency code ▲▼", "Currency name ▲▼", "Units per USD", "USD per Unit"}, {"Custom.Currency code ▲▼", "Custom.Currency name ▲▼", "Custom.Units per USD", "Custom.USD per Unit"}) in #"Expanded Custom"
Here is the sample pbix file for your reference.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |