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.
Hi,
I'm Data scraping from the web rates from the Federal Reserve (https://www.federalreserve.gov/releases/h15/default.htm) in Excel and trying to figure out how to automate gathering data. As you can see from the website, there are six columns that represent the data starting with "Instruments" and then going into a 5-day period. However, the 5-day period changes almost everyday.
My issue is that when I create the table it works fine, but the next day when new rates become available and a new date succeeds the previous date, under Excel's Data tab I click "Refresh All", I get "[Expression.Error] The Column '2021 Nov 19' of the table wasn't found." Clearly because Nov 19, the date of the column, is no longer there and a newer date is available.
Does anyone know how I can make this dynamic? For example, if the columns are [Instruments, 2021 Nov 19, 2021 Nov 22, 2021 Nov 23, 2021 Nov 24, 2021 Nov 25*] and the next day they become [Instruments, 2021 Nov 22, 2021 Nov 23, 2021 Nov 24, 2021 Nov 25*, 2021 Nov 26], how do I make the Data Scrape Refresh pull and/or overwrite the existing tables/columns data without having to create a new table every time?
I've tried:
Current Advanced Editor Code:
let
Source = Web.Page(Web.Contents("https://www.federalreserve.gov/releases/h15/")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Instruments", type text}, {"2021 Nov 19", type text}, {"2021 Nov 22", type text}, {"2021 Nov 23", type text}, {"2021 Nov 24", type text}, {"2021 Nov 25*", type number}})
in
#"Changed Type"
Below is what i'm working with:
Solved! Go to Solution.
The simplest approach is to just remove that step entirely.
let
Source = Web.Page(Web.Contents("https://www.federalreserve.gov/releases/h15/")),
Data0 = Source{0}[Data]
in
Data0
If you want to actually do stuff with those columns like filter out blanks and "n.a.", then you can unpivot, clean up, and repivot. Here's an example:
let
Source = Web.Page(Web.Contents("https://www.federalreserve.gov/releases/h15/")),
Data0 = Source{0}[Data],
#"Added Index" = Table.AddIndexColumn(Data0, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Instruments", "Index"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "" and [Value] <> "n.a.")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}})
in
#"Sorted Rows"
The simplest approach is to just remove that step entirely.
let
Source = Web.Page(Web.Contents("https://www.federalreserve.gov/releases/h15/")),
Data0 = Source{0}[Data]
in
Data0
If you want to actually do stuff with those columns like filter out blanks and "n.a.", then you can unpivot, clean up, and repivot. Here's an example:
let
Source = Web.Page(Web.Contents("https://www.federalreserve.gov/releases/h15/")),
Data0 = Source{0}[Data],
#"Added Index" = Table.AddIndexColumn(Data0, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Instruments", "Index"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "" and [Value] <> "n.a.")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}})
in
#"Sorted Rows"
Ah! Such a simple solution.. Thank you!
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |