cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harmonrichmond
New Member

Expression Error The Column "ColumnName" of the table wasn't found

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:

  • Under the Query Editor, Demoting column headers and making it so the first row is just column names, for example instead of "Instruments" it would say "Column 1" and then "instruments" would be below "Column1", and so forth for the dates. 
  • Under Table Design>Properties, changing the different checked boxes.
  • Looking at other threads, but can't seem to find a similar issue or resolution

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:

harmonrichmond_1-1638295858895.png

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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"

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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"

View solution in original post

Ah! Such a simple solution.. Thank you!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors