Hi Team,
I am using dynamic parameter by following the microsoft doc. Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Docs
Using the document I have bing the parameter with the slicer that contains the values like: ABC, XYZ, PQR and also like: ABC's, XYZ's, PQR's.
I have successfully created the report and it is working fine with values like: ABC or PQR or XYZ
but when I am selecting XYZ's from slicer the SQL statement is not working properly.
Below is the Power Query script: Here the Para_ColumnName is a Parameter created to Bind the slicer.
let
//===================================================================================PART 1=======================================================//
selected_ColumnName = if Type.Is(Value.Type(Para_ColumnName), List.Type) then
Text.Combine({"'", Text.Combine( Para_ColumnName , "','") , "'"})
else
Text.Combine({"'" , Para_ColumnName , "'"}),
selectAll_ColumnName = if Type.Is(Value.Type(Para_ColumnName), List.Type) then
List.Contains(Para_ColumnName, "__SelectAll__")
else
false,
//===========================================================================================================================================//
All_ColumnName = if selectAll_ColumnName or selected_ColumnName="'ABC'" then "A" else "B",
//===========================================================================================================================================//
FinalQuery = if All_ColumnName ="A" then Source else Source1,
//=====================================================================================PART 2========================================================//
Source = Value.NativeQuery(Snowflake.Databases("","",[Role=""]){[Name=""]}[Data],
"
SELECT TOP 1000
List of Columns
FROM DBNAME.SCHEMANAME.TABLENAME
", null, [EnableFolding=true]),
//============================================================================================================================================//
Source1 = Source = Value.NativeQuery(Snowflake.Databases("","",[Role=""]){[Name=""]}[Data],
"
SELECT
List of Columns
FROM DBNAME.SCHEMANAME.TABLENAME
WHERE COALESCE (TRIM(UPPER(ColumnNAME)),'NA') IN ("&selected_ColumnName&")
", null, [EnableFolding=true]),
#"Removed Duplicates" = Table.Distinct(FinalResult)
in
#"Removed Duplicates"
1. When I Apply the slicer with values like:ABC or XYZ then this query works fine.
but when I use values like: XYZ's or ABC's then it gives me error
2. I have find the issue also, that the where clause is failed to escape the single quotes
WHERE COALESCE (TRIM(UPPER(ColumnNAME)),'NA') IN ("&selected_ColumnName&")
3. can you please guide me on how can i make changes in PART1 that can help to escpae the single quotes.
4. I have written below script to escape but its not working and giving error: which states, "Can not Conver the TYPE LIST to TYPE TEXT"
Single_quotes = if Text.Contains(Para_ColumnName,"'") then Text.Replace(Para_ColumnName,"'","\'") else Para_ColumnName,
But I dont know How to use Single_quotes in PART ONE.
