How to rebuild data combination Power Query

0
votes

In Power Query, I want to use a list of distinct values from one query (e.g. list of customers present on "Sales" table), to inject it on a SQL statement on another query (e.g. "Customer" dimensional table).

To pull the list of distinct values I have a function, getDistinct() that:

  1. Retrieves one column from a query choice,
  2. Only keep distinct values present on that column, and
  3. Return these distinct values separated by commas so they can be injected within an SQL statement.

This function works fine on a standalone query. However, when I try to use it on my "Customer" query it throws an error (see code and error below):

let
    Source = Oracle.Database("myServer", [Query="select * from db_customer where customer_id in (" & getDistinct(Sales,"CustomerID") & ")"])
in
    Source

And the error:

Formula.Firewall: Query 'Customer' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I've tried creating a different query that executes the function and then referencing it on my "Customer" query, but this doesn't seem to work. I know I can "Ignore Privacy Levels" (which by the way, I've checked and works), but since I don't know the implications of it, I'm afraid of leaked data.

1
powerquery
for "I don't know the implications of it, I'm afraid of leaked data" see: docs.microsoft.com/en-us/power-query/dataprivacyfirewall – ninMonkey

1 Answers

3
votes

I don't see why a function or any hand-written code is necessary for this requirement.

I would create a Query to get the Sales table and then Group by CustomerID. I would set that to: Load To / Only Create Connection.

Then the Customers Query would just be:

  1. Source is Oracle Customers table
  2. Merge to Sales Query on CustomerID, with Join Kind = Inner