Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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"
Anonymous
Not applicable

Ah! Such a simple solution.. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.