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
MAXIM_UVAROV
Frequent Visitor

Power BI Error: the underlying connection was closed an unexpeted error occured on a send power bi

Hello!

I have m-script which retrieves data from one custom web api. 

This script works perfectly in power query under excel, but when I try to run it under the latest Power BI from time to time it gives me an error:

 

But sometimes works well and it retrieves data. I can't figure when it gives an error. 

I've send a frown. 

 

How can I repair it? 

13 REPLIES 13
pqian
Employee
Employee

@MAXIM_UVAROV, One thing to start doing is capturing web traffic traces using Fiddler. The error seems to be server side.

@pqian If the problem was on sever side, I wouldn't be able to get data in Excel. But I'm gettin this error in power bi only, not in Power Query for Excel

@MAXIM_UVAROV have you tried capturing the HTTP traffic in Fiddler? That should give us some insight of what may be happening.

 

As a side note, PowerQuery in Excel and PowerBI desktop uses the same ETL engine, the only difference is the installed version between the two. 

I got Fiddler installed. Now I need to understand how it works:)

I run m-script in PowerBI and see two sessions in Fiddler. On statistics tab I see next

 

This is a Tunnel. Status: CLOSED, Raw Bytes Out: 185; In: 0

The selected session is a HTTP CONNECT Tunnel. This tunnel enables a client to send raw traffic (e.g. HTTPS-encrypted streams or WebSocket messages) through a HTTP Proxy Server (like Fiddler).


To enable Fiddler's HTTPS-decryption feature and view decrypted traffic, click Tools > Fiddler Options > HTTPS.


Request Count:   1
Bytes Sent:      75		(headers:75; body:0)
Bytes Received:  180		(headers:180; body:0)
Tunnel Sent:     185
Tunnel Received: 0

What shall I do to provide all the necessary information? 

Turn on decryption on Fiddler first.

 

You are looking for a server error that should be returned as you refresh. Typically they give you a status code and a message explaining what's going on

I got:

fiddler.network.https> HTTPS handshake to api-metrika.yandex.ru (for #2) failed. 
System.IO.IOException Unable to read data from the transport connection:
An existing connection was forcibly closed by the remote host.
< An existing connection was forcibly closed by the remote host

What it means? 

@MAXIM_UVAROV That means the server at yandex.ru has rejected the connection. Probably due to some API error, and it maybe something you sent isn't correct. Fiddler also captures the outgoing traffic (the upper right window is out going, lower right window is inbound)

 

What's was in the out going traffic (RAW view)? Can you check all parameters (especially the API key and the auth token) in the outgoing traffic is expected?

 

Another thing that would help: post the PowerQuery formula?

The thing is that the function works from time to time. 

 

It worked previously without problems, but now most of the time It gives an error (and sometimes it runs succesfuly).

 

The function that I do is relatively simple:

It takes ids, tokens and other config on input.

After it creates simple url and get the data from its url using Web.Contents. 

 

When it gives me an error - PowerBI gives me message with url that it can't retrieve. 

And thing is that if I paste this url into browser it works!

 

If you would like you can try it yourself:
The configured call of funciton is:

 

= getDataFromMetrikaFunction("21781912","ym:s:<attribution>SearchPhrase","ym:s:visits,ym:s:bounceRate,ym:s:pageDepth,ym:s:avgVisitDurationSeconds,ym:s:anyGoalConversionRate","2016-01-15","2016-02-12","0aa2c251c2264c0e94eff1348eed63be")

 

and the funciton is (you'll need to name it getDataFromMetrikaFunction) 

 

/*
     Функция, при помощи которой мы забираем из API данные из Яндекс.Метрики
     Версия 1.03

     metrikaFunction = (ids, dimensions, metrics, date1, date2, token, filters)
     Все значения передаются как text. 
     На выходе получается таблица с запрошенными полями.  

     Домашняя страница: 
     https://github.com/40-02/PQYandexMetrika/
    
     Справка по api метрики:
     https://tech.yandex.ru/metrika/doc/api2/concept/about-docpage/
    
*/

let
    metrikaFunction = (ids as text, dimensions as nullable text, metrics as text, date1 as text, date2 as text, token as text, optional filters as text) => 




        // Определяем функцию metrika_fun внутри функции, которая будет вытаскивать CSV из API. 
        // На входе конфигурационная запись (формируется в основном теле программы ниже по коду).
let
    metrika_fun = (bigRecordWithOptions as record, X as number) =>
let
    offset = Number.ToText (X),
    bigRecordWithWithOffset = Record.AddField(bigRecordWithOptions, "offset", offset),
    bigRecordWithWithLimit =  Record.AddField(bigRecordWithWithOffset , "limit", "10000"),
    urlToGet = "https://api-metrika.yandex.ru/stat/v1/data.csv?" & Uri.BuildQueryString (bigRecordWithWithLimit),

    Source = Csv.Document(Web.Contents(urlToGet),null,",",null,65001),
    #"First Row as Header" = Table.PromoteHeaders(Source),

// Для отчетов без измерений 

    mergedColumns = if Table.RowCount(#"First Row as Header") = 1 then #"First Row as Header" else Table.Skip(#"First Row as Header",1)

in
    mergedColumns,



        // Определяем функцию metrika_json внутри функции, которая будет выгружать json версию отчета и возвращать из json максимальное количество строчек
    metrika_json = (bigRecordWithOptions as record) =>
let
    urlToGet = "https://api-metrika.yandex.ru/stat/v1/data?" & Uri.BuildQueryString (bigRecordWithOptions),

    Source6 = Json.Document(Web.Contents(urlToGet &"&limit=1")),
    Source7 = Source6[total_rows]
in
    Source7,


/////////// Основной код функции начинается здесь. //////////



        // Формируем конфигурационную запись (record) для использования в функциях
    bigRecordWithOptions = [ids = ids, dimensions = dimensions, metrics = metrics, date1 = date1, date2 = date2, oauth_token = token, accuracy = "full"], 
    bigRecordWithFilters = if filters = null then bigRecordWithOptions else Record.AddField(bigRecordWithOptions, "filters", filters), 

        // Создаем список из чисел - сколько раз нам необходимо обратиться к api чтобы забрать по 10к строчек все данныые которые есть в метрке согласно нашим настройкам. 
    Source0 = Number.RoundDown(metrika_json(bigRecordWithFilters)/10000,0),
    Custom1 = {0..Source0},
    Custom2 = List.Transform(Custom1, each _ * 10000 + 1),

        // Используем список получившихся чисел, чтобы обратиться к api
    Custom3 = List.Transform(Custom2, each metrika_fun(bigRecordWithFilters, _)),

        // Создаем список с названиями заголовков полей
    TableNames = Table.ColumnNames(Custom3{0}),

        // Разворачиваем таблицы в списке с данными из Яндекс.Метрики и используем список с названиям заголовков, которые мы получили ранее
    #"Converted to Table" = Table.FromList(Custom3, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", TableNames )

in
    #"Expanded Column1"
in
    metrikaFunction

 

Has anybody resolved this issue?

Previously I posted that the same function works in excel without problems.

But now I found that it works in the latest excel 2016 with problems either 😞

 

And still if I paste produced url into browser - I get data without problems!

Did you ever resolve this? I'm having the same problem.

Hi Maxim,


I don't know what changed in the product that could cause the difference in the behavior. It reproduces when using Fiddler, which seems to indicate that the service is closing the connection based on the HTTP request. Can you use Fiddler with PBI Desktop and Excel to determine what is different in the request? There may be something different, such as header.

 

Tristan

 

And btw a representative of Microsoft sent me a reply on my frown:

 

 

Thank you for the feedback. I’ve filed the bug and we’ll look into the issue. We are working on fixing the issues and will be sending out regular updates, so be on the lookout! 

 

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.