Forum Discussion

jmadsen's avatar
jmadsen
Frequent Visitor
3 years ago

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

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...
Need Help
edhans's avatar
edhans
Community Champion
3 years ago

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's avatar
jmadsen
Frequent Visitor
3 years ago

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"