Intermittent error - Expression.Error: The column 'Column1' of the table wasn't found.

[mart1fio]

I have a web service I am using as my data source. The first step is to convert the list to a table, and then expand the columns, it is in this step that Column1 appears in the Advanced Editor, I have pasted part of the line below;

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"auditId", "date",

I have the option in this step to use Column1 in the column names which I don't do.

The next step does refer to Column1 (see below) but when I click on the "Go to Error" it takes me to the line above.

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"date", type datetime}}),

None of my columns have the name Column1 and the table name (auto created) is the URL.

I finish all my work in Power query, load the table, create visuals etc. Sometime when I refresh the data I get Expression.Error: The column 'Column1' of the table wasn't found and other times it goes through with no problems. Same with my auto refresh, sometimes it gets that error, other times it works so I know the problem isn't with my query.

What do I need to do to fix this?

[mart1fio]

The issue turns out to be a time out on the server,. The webservice has a default last 2 year period of data it pulls. By putting in a specfic shorter period we can get it tob work consistently. The vendor who created the web service for me is looking at this to see if they can optimise the query.

[rachelgomez1234 · Accepted solution]

Ways to fix-

1. Accessing and capturing the packets

The first step in setting up the IOTA to capture packets is to configure the capture ports in in-line mode. Login to the IOTA and turn off SPAN mode which puts it in in-line mode.

IOTA inline mode

Place the IOTA 1G inline between the client PC and the rest of the network. The IOTA 1G supports full line rate and full duplex gigabit traffic. Not only will it capture the traffic, but the TAP is fault tolerant. Therefore, if the IOTA loses power for any reason, it will continue to pass packets between the computer and the rest of the network.

After placing the IOTA in line, press the button to start capturing all the packets. With IOTA, all the data can be stored for a long period of time. This is important to successfully resolve intermittent problems, because it is difficult to detect when the problem's going to occur and a small capture buffer will reduce time window as well. IOTA also has a 1TB SSD drive built in. For a typical computer, this means, that the data can be captured for days or weeks without losing any data. As the packets are captured, the IOTA is writing them to files and storing metadata about each flow in a searchable database.

2. Fast issue identification

In the past, the person having this issue would need to write down the date and time when the problem occurred. This was very unreliable. A better way to mark the capture to show when the problem occurs is to put a shortcut on desktop that will send a ping packet each time the person having the issue clicks on it, go back later and search the captured packets for the ping to find out when the problem occurred.

Mark the capture

3. Drill-down to the packet marker

Let's look at how to find the marker and extract the packets to analyze them in Wireshark. Just start looking at a 24-hour period would result in a lot of captured data. By setting a filter on the IP address of the marker frame, you will be able to find the marker packet and see exactly when the client clicked on the shortcut. Using the mouse to zoom in 10 minutes before and 10 minutes after the marker, will allow you to see what happened before and after the problem occurred.Find the marker

Afterwards, remove the marker filter and set a filter on the IP address of the device having problems. This is as easy as clicking on the magnifying glass next to the IP address. When done correctly, you will have only a 110MB of traffic going across the network during that time period. Clicking on download pcap will extract those packets to and from the problem PC during the time period to your computer.

4. Analyze the captured packets in Wireshark

Next, open the trace file up in Wireshark by applying an IP address filter to the trace file, you will find the market packet. In this case, the first marker occurred at frame 22069. This is where you should start looking in the trace file. Click on the frame and remove the filter to see all the traffic the IOTA captured between the client computer and the rest of the network. It’s just a matter of digging through the trace before the marker to find the issue.

4.44

IOTA helps to get to the root of intermittent problems by getting in the path of the packets capturing at full line rate, providing an easy means to filter down on problem packets and making it easy to extract those packets for network traffic analysis.

Regards,

Rachel Gomez

[mart1fio]

Thanks for this, working with the person providing the webservice it looks like the query is timing out on the server. We can get itt to work if we use a shorter timeframe.

[amitchandak]

@mart1fio , Please share 2-3 lines of code. There can be some other issue, leading to this error

Like missing comma at end of the line

line code has error