Can I reference a [table].[field] list imported into Power BI inside a sql where clause

[Anonymous]

I have an excel spreadsheet that I've imported as a table to Power BI. One of the columns is AcctNumber. I need to bring in an IDNumber for each AcctNumber.

I would normally find the IDNumber from a table in our sql server using the sql query:

Select distinct AcctNumber, IDNumber
From Table
where AcctNumber in ('AcctNumber1', 'AcctNumber2', 'AcctNumber3').....etc.

I can connect to the sql server from Power BI but I don't want to manually update the where clause every month.
The excel spreadsheet can have thousands of distinct AcctNumber and the sql server table has millions of IDNumbers.
I need this to be efficient as I can't be returning millions of rows just to merge a few thousand.

Is there a way to replace the contents of in() in the where clause with a reference to the [table].[field] list from the table I imported from Excel?

It would look something like this:

Select AcctNumber, IDNumber
From Table
where AcctNumber in ([imported table].[AcctNumber field])

I've searched the forums and have not been able to find a solution for this situation. Thank you in advance.

[Anonymous]

Hi @Anonymous ,

Please try like:

let
    // Load the Excel data
    ExcelData = Excel.Workbook(File.Contents("excelfilepath"), null, true),
    AcctNumber_Table = ExcelData{[Item="xxx",Kind="xxx"]}[Data],
    AcctNumber_List = List.Transform(AcctNumber_Table[AcctNumber], Text.From),

    // Create a comma-separated list of account numbers
    AcctNumber_Text = Text.Combine(AcctNumber_List, ","),

    // Construct the SQL query
    SqlQuery = "SELECT AcctNumber, IDNumber FROM Table WHERE AcctNumber IN (" & AcctNumber_Text & ");",

    // Execute the SQL query
    Result = Sql.Database("your_server", "your_database", [Query=SqlQuery])
in
    Result

About Formula Firewall:
Chris Webb's BI Blog: Detailed Article On The Power Query Formula Firewall And Data Privacy Settings (crossjoin.co.uk)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Anonymous]

Thank you Gao for your suggestion! Everything works until I get to the Execute Query portion. I can see the concatenated list and copied it to our sql server to run it there and it returns data correctly so I know the query is good. But within power query, I get the error below. I'm trying to pull in ~550 AcctNumbers. Is there a limit of what can be included in the power query list? Or is something else goin on? Thank you!

Expression.Error: We cannot convert a value of type Record to type Text.
Details:
Value=
Query=SELECT DISTINCT .....my query...

Type=[Type]