cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAXIM_UVAROV Frequent Visitor
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 Senior Member
Senior Member

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

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

MAXIM_UVAROV Frequent Visitor
Frequent Visitor

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

@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

pqian Senior Member
Senior Member

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

@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. 

MAXIM_UVAROV Frequent Visitor
Frequent Visitor

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

I got Fiddler installed. Now I need to understand how it worksSmiley Happy

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? 

MAXIM_UVAROV Frequent Visitor
Frequent Visitor

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

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! 

 

pqian Senior Member
Senior Member

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

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

MAXIM_UVAROV Frequent Visitor
Frequent Visitor

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

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? 

pqian Senior Member
Senior Member

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

@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?

Highlighted
MAXIM_UVAROV Frequent Visitor
Frequent Visitor

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

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:sSmiley TongueageDepth,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