Is there an Alternative to Keylist?

[mjjuk]

I have a list that gets populated in a sharepoint csv file which I convert in to a key list in order to reference a ODBC table .... for some reason it has stoped working .. but I was wondering if there is another method to reference the data instead of the WHERE IN clause ??

let
Source = SharePoint.Files("https://#########.sharepoint.com/sites/#####", [ApiVersion = 15]),
#"_Data csv_https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/" = Source{[Name="Data.csv",#"Folder Path"="https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/"]}[Content],
#"Imported CSV" = Csv.Document(#"https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/",[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Inserted Merged Column" = Table.AddColumn(#"Promoted Headers", "Merged", each Text.Combine({"'", [data_no], "'"}), type text),
keylist= Text.Combine(#"Inserted Merged Column"[Merged],","),

Source2 = Odbc.Query("dsn=########_powerbi",
"SELECT
a.column1,
a.column2
FROM database.table a
WHERE a.column1 IN (" & keylist & ");")
in
#"Source2"

[mjjuk]

Im not sure if I can use this in the SQL code 

Would you know how to construct it instead of "WHERE a.column1 IN (" & keylist & ");")"  ?

[v-jianboli-msft]

Hi @mjjuk ,

If you want to check if column contains any value from another table's column, please consider List.ContainsAny

Here is a similar thread about this problem.

Please check if these could help you a little.

Solved: Check if column contains any value from another ta... - Microsoft Power BI Community

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mjjuk]

Thanks Jianbo, 

The problem is I am trying to create a keylist from Sharepoint (or Salesforce) and then look into a ODBC Datalake for the raw data 

My current workaoround was to use the first source pointed to the sharepoint column I needed then convert into the keylist ... then in the same query then go to my odbc data and add the SQL 

if the data was all in the Datalake and I was writing the query I would likely write it with a join which would improve the efficiancy of the query (I have been told that "WHERE IN" is not very good for large data sets)

I`m just looking for a better solution to this 

let
Source = SharePoint.Files("https://#########.sharepoint.com/sites/#####", [ApiVersion = 15]),
#"_Data csv_https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/" = Source{[Name="Data.csv",#"Folder Path"="https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/"]}[Content],
#"Imported CSV" = Csv.Document(#"https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/",[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Inserted Merged Column" = Table.AddColumn(#"Promoted Headers", "Merged", each Text.Combine({"'", [data_no], "'"}), type text),
keylist= Text.Combine(#"Inserted Merged Column"[Merged],","),

Source2 = Odbc.Query("dsn=########_powerbi",
"SELECT
a.column1,
a.column2
FROM database.table a
WHERE a.column1 IN (" & keylist & ");")
in
#"Source2"

[mjjuk]

This is wrong but I was thinking of something like: 

let
Source = SharePoint.Files("https://#########.sharepoint.com/sites/#####", [ApiVersion = 15]),
#"_Data csv_https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/" = Source{[Name="Data.csv",#"Folder Path"="https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/"]}[Content],
#"Imported CSV" = Csv.Document(#"https://#########.sharepoint.com/sites/#####/Shared Documents/Data Files/",[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),

Source2 = Odbc.Query("dsn=########_powerbi",
"SELECT
a.column1,
a.column2
FROM database.table a
RIGHT JOIN source ON a.column1 = column1
in
#"Source2"