Loading data - function define source

[Dan_S]

Hello, I don't know how to solve the problem that I described below.

I'm loading data from SQL Server and I'm having a problem when loading this data through a gateway in an online environment.

1. First, I use a select statement to retrieve data from the SQL database
2. Then I create a list of values from it
3. Next I use a function to generate a source query to the other SQL database, which includes a "where in" statement based on the list of values
4. In the desktop version, everything works fine, but in the online environment, I can't perform the update

The purpose is not to load all data from SQL database and then use filter but only get the data that interests.

I have all in new version.

Can sameone help me solve this problem?

Thank you very much

Dan

[Dan_S]

Oh, thank you. Dint know about back folding to the server. Thank you

[BA_Pete]

Hi Dan,

For starters, your method of reducing data in Power BI is incredibly inefficient. You can import your first SQL data into Power Query then perform the transformations you need and these will fold back to the server, so won't ever actually bring in all the data.

Once you have your first list (Table1), you should just be able to buffer Table1[ListColumn] and send it to the second DB within the folded native query by using a table filter something like this:

Table.SelectRows(
    Table2PreviousStep,
    each List.Contains(List.Buffer(Table1[ListColumn]), [Table2FilterColumn])
)

Secondly, when you say "in the online environment, I can't perform the update", what does this actually mean? Do you get a processing error? An authentication error? Nothing happens? Etc.

Pete