Get an entire column as a reference to put in a SQL query

[jmadsen]

Hi there.  I may not be doing this the best way, so I wanted to get some feedback on a potential better way to do this. Essentially, I have a spreadsheet that gets downloaded from an external website.  It includes customer IDs in our database.  I want to place those customer IDs into a SQL Query within an in-statement.  Example "WHERE CustomerID in (ID1, ID2, ID2, and so forth)."  Since the in-statement required commas between them, what I'm currently doing is using the following Excel formula to grab the customer IDs from the column, concatenate them with a comma, and return that to a cell.  Formula is =TEXTJOIN(",",TRUE,E2:E100487).

I then use power query to grab that cell as a reference called CustIDs and, and put it in the SQL Statement that runs in power query.

This works great up to a certain amount of Customer IDs.  About 120.  After that, there's a limit to the number of characters that can go into a cell.  So, this works well for small daily batches, but I'd like to be able to run this for larger batches.

So, is there a way in power query to grab the contents of a column, concatenate them with commas, and then assign them to a variable to put in my query like above? 

[ehanback]

OP, did you ever find a solution? I'm trying to do the same thing.

[jmadsen]

Yes, there is a way to grab the contents of a column in Power Query, concatenate them with commas, and assign them to a variable to use in your query. Here's how you can do it:

1. Load the Excel spreadsheet into Power Query by clicking on the "Data" tab in Excel, then selecting "From Table/Range".
2. In Power Query, select the column containing the customer IDs and click on the "Transform Data" button.
3. In the "Transform Data" window, select the "Text Filters" dropdown and choose "Does Not Equal".
4. In the "Custom" field, enter a single character that is not present in any of the customer IDs (e.g. "~").
5. Click on "OK" to filter the data.
6. Select the filtered column and click on the "Replace Values" button in the "Transform" tab.
7. In the "Replace Values" window, replace the character you used to filter the data with a comma.
8. Click on "OK" to replace the values.
9. Click on the "Close & Load" button to load the data into a table in Excel.

Now that you have the customer IDs concatenated with commas in a table, you can create a new query that references this table and uses the customer IDs in your SQL query. Here's an example:

1. Click on the "Data" tab in Excel, then select "From Table/Range".
2. In Power Query, select the table containing the concatenated customer IDs and click on the "Transform Data" button.
3. In the "Transform Data" window, click on the "Add Column" tab and select "Custom Column".
4. In the "Custom Column" window, enter a name for the column (e.g. "CustomerIDs") and enter the following formula: =Text.Combine(Table.Column(#"Changed Type", "CustomerID"),",")
5. Click on "OK" to create the custom column.
6. Click on the "Close & Load" button to load the data into a table in Excel.

Now, you can reference the "CustomerIDs" column in your SQL query by using the following syntax: WHERE CustomerID in ( $(CustomerIDs) ). Make sure to replace "CustomerIDs" with the name of your custom column. When you run the query, Power Query will replace "$(CustomerIDs)" with the concatenated customer IDs from the table, allowing you to use as many customer IDs as you need without hitting the character limit of a cell.

[edhans]

Not 100% sure what you are asking, but you can take a list of text values from a column, concatenate them, and assign to a variable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUcpKTUtTitWJVkoCcnITi7LBnGQIpxLMSQFyiktSy1KVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Name = _t]),
    AllNames =
        Table.AddColumn(
            Source,
            "All Names",
            each
                let
                    varAllNames = Text.Combine(Source[Name], ", ")
                in
            varAllNames
        )
in
    AllNames

 So the varAllNames variable can be returned or further acted upon by other functions.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[jmadsen]

Thanks for the quick response.  Sorry if my question wasn't very clear.  I'm very new to power query and M Code.  Basically, I need the numbers in the column called Reference Id turned into a comma-separated variable that I can then reference in a SQL Query.

Here is my M Code thus far:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Review Id", "Review Consumer User Id", "Review Username", "Review Title", "Review Content", "Company Response", "Company Response Author", "Review Language", "Domain Url", "Webshop Name", "Business Unit Id", "Tags", "Company Reply Date (UTC)", "Location Name", "Location Id"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Reference Id] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Reference Id", Int64.Type}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Reference Id"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Errors", "5 Star Count", each if [Review Stars] = 5 then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Survay Count", each if [Review Stars] <= 5 then 1 else 0),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "1 Stars", each if [Review Stars] = 1 then 1 else 0)
in
    #"Added Conditional Column2"

that I can then place into here where it currently references &CustIDs& in the SQL Query:

let
    Source = Sql.Database("PagListener02", "CustSrv", [Query="SELECT C.CustomerID,
   CT.CreateUser
FROM Customer C
INNER JOIN CustomerTransaction CT ON C.CustomerID = CT.CustomerID
WHERE CT.TransactionCode = 'Install'
And CT.StatusCode <>'c' 
And C.CustomerID in("&CustIDs&")"]),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"CreateUser", Text.Lower, type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Lowercased Text", {"CreateUser"}, Roster1, {"Agent"}, "Roster1", JoinKind.RightOuter),
    #"Expanded Roster1" = Table.ExpandTableColumn(#"Merged Queries", "Roster1", {"Name", "Supervisor"}, {"Roster1.Name", "Roster1.Supervisor"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Roster1",{"CustomerID", "Roster1.Name", "Roster1.Supervisor", "CreateUser"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Roster1.Name", "Consultant"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"CustomerID", "Consultant", "CreateUser", "Roster1.Supervisor"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"CreateUser", "User ID"}, {"Roster1.Supervisor", "Team"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns1", {"CustomerID"}, #"Data Output", {"Reference Id"}, "5 Stars", JoinKind.LeftOuter),
    #"Expanded 5 Stars" = Table.ExpandTableColumn(#"Merged Queries1", "5 Stars", {"1 Stars", "5 Star Count", "Review Stars", "Survay Count"}, {"1 Stars", "5 Star Count", "Review Stars", "Survay Count"}),
    #"Grouped Rows" = Table.Group(#"Expanded 5 Stars", {"Consultant", "User ID", "Team"}, {{"5 Stars", each List.Sum([5 Star Count]), type number}, {"Survays", each List.Sum([Survay Count]), type number}, {"1 Stars", each List.Sum([1 Stars]), type number}, {"Total", each List.Sum([Review Stars]), type number}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Grouped Rows",{"Consultant", "User ID", "Team", "5 Stars", "1 Stars", "Total", "Survays"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns2", "Trust Pilot Average", each [Total]/[Survays])
in
    #"Added Custom"