Problem with Power Query refresh in Power BI Service (httpStatus :500)

[InsightDriven11]

Hello everyone,

I’m getting this error message when I refresh Power Query via the Service:

Underlying error: PowerBI service client received error HTTP response.
HttpStatus: 500
PowerBIErrorCode: ExternalServiceFailed

I created a function that retrieves data from 20 different databases:

(Base as text,   requete as text) as table =>

let

    Source =

        try

            Sql.Database(

                serveur,

                Base,

                [

                        Query=

                         query

                ]

            )

        otherwise

            #table({}, {}),

    AddBase = Table.AddColumn(Source, "SourceBase", each Base)

in

    AddBase

Then I call this function to build a query that concatenates the data into a single table:

let

    BasesActives = Table.SelectRows(Database_table, each [Active] = "1"  ),

    AddData = Table.AddColumn(

        BasesActives,

        "Data",

        each getTableSQL(

            [BaseName],

            "select CG_num  from dbo.F_ECRITUREC"

        )

    ),

    Combine = Table.Combine(AddData[Data])

in

    Combine

I’m able to preview the data and everything looks fine. However, the data does not load when I try to save/publish (and i have the error 500, that we can see above..) 

I tested the same query on a single database without using the function, and the data loads correctly.

Has anyone experienced the same issue? any idea to solve it ?

Thanks a lot, 

Martin 

[v-prasare]

Hi @InsightDriven11,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[InsightDriven11]

Hi, 

Thank you for your answer ! 

I tested the code above, with only 1 database in Database_table

The display is perfectly working, but the load not.

In addition, i'm not working in Fabric, but in Power BI Service.

[AjiteshChurewal · Accepted solution]

Hi,

Since the data loads successfully for a single database but fails when running for 20 databases, this could be a timeout issue. How much time does it take to extract data from one database?

I suggest trying the following:

1. Gradually increase the number of databases one at a time (i.e., first run for 1 database, then 2, and so on) and identify the point of failure — specifically, after how many databases the error starts occurring. This could indicate two possibilities:

   • The error is a timeout issue (in which case, proceed to step 2).

   • There is an issue fetching data from a particular database. To rule this out, try running the process only for that specific database. If it runs successfully, then this is not the cause, and you can proceed to step 2.

2. Create separate dataflows for each database (resulting in 20 dataflows). Then use these dataflows as sources, merge them into a single dataflow, and use that for reporting. If you are using Fabric capacity, use Dataflow Gen2; otherwise, use Dataflow Gen1.

_________________________________________________________
If this helped, ✓ Mark as Solution | Kudos appreciated