PowerBI Service error: OData: Unable to read data from the transport connection:

Hi,

We have also been experiencing this mysterious and frustrating error over the past few months, with several clients. While we haven't been able to find the exact cause (other than that it seems to occur with heavy data load or too heavy queries), we have found a workaround that so far has stopped the errors.

We created a dataflow where we import all the tables we need from BC. Then we applied as many query steps as possible (non premium capacity doesn't allow merging or appending), to reduce the steps needed in Power Query. In the pbix file we then connect to the dataflow and load in all the partially complete tables. For each table we then created a new query, referencing these "import" tables as source, and applied the rest of the necessary query steps. So for example we might have a "SalesImport" table from the dataflow and a final "Sales" table to be used in the model. We then turned off load for the import tables, leaving just refresh on. 

Finally, we scheduled the dataflow to refresh 1-2 hours prior to the report refresh, to make sure that the dataflow is always done before the main report loads from it. With this setup, we haven't had any more errors. Refresh times can still be long (anywhere from 10 minutes to an hours in our case), but it always completes.

Hope this can be helpful for the rest of you too 🙂