cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarceloSilva5_
Frequent Visitor

How to pass a empty parameter in URL filter query string

Hello i have a question:

I created this query:

 

(Name as text,datetimestart as text, datetimeend as text, listzones as text, listaps as text, listdevices as text, listuserprops as text, topreg as text) as table =>

let
    Source = Csv.Document(Web.Contents("https://localhost:44310/api/Emp?Name=" & Name & "&datetimestart=" &  datetimestart & "&datetimeend=" & datetimeend & "&listzones=" & listzones & "&listaps=" & listaps & "&listdevices=" & listdevices & "&listuserprops=" & listuserprops & "&topreg=" & topreg &  ""),[Delimiter=":", Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Source

 

In total i have 8 parameters and i need to pass to the URL some empty parameters like "listdevices and listuserprops"
But when i try to do it i receive the message "Please specify a value for listuserprops"

Annotation 2022-06-12 231728.png

 

Is there a way to pass a empty parameter and how  ?


Thanks!!!


 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @MarceloSilva5_ ,

Please try to update the codes of parameter listdevices and listuserprops as below and check if it can work or not:

 

null meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=false]

 

yingyinr_0-1655263202294.png

And you can get the details of query parameter in the following links.

Creating Dynamic Parameters in Power Query

yingyinr_0-1655270841233.png

Best Regards

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

View solution in original post

4 REPLIES 4
yingyinr
Community Support
Community Support

Hi @MarceloSilva5_ ,

Please try to update the codes of parameter listdevices and listuserprops as below and check if it can work or not:

 

null meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=false]

 

yingyinr_0-1655263202294.png

And you can get the details of query parameter in the following links.

Creating Dynamic Parameters in Power Query

yingyinr_0-1655270841233.png

Best Regards

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

My Stored procedure :

 

if (@Name = 'UniqueVisits')
            begin
                  set @BaseQuery = 'select count(distinct fp.device_id) as NVisits from f_presence fp '

				  if (@listzones is not null)
				  begin
					--set @includeap = 1
					set @QueryFilter = @QueryFilter + ' and coalesce(fp.zone_id,-1) in (' + @listzones + ')'   
				  end

				  if (@listaps is not null)
					set @QueryFilter = @QueryFilter + ' and coalesce(fp.ap_id,-1) in (' + @listaps + ')'

				  

                  set @QueryFilter = ' WHERE ((fp.datetime_start < ' + cast(@datetimeend as nvarchar(12)) + ' and fp.datetime_end > ' + cast (@datetimestart as nvarchar(12)) + ') OR fp.datetime_start = ' +  cast (@datetimestart as nvarchar(12)) + ')' + @QueryFilter 
                  
                  if (@includedevice = 1)
						set @TablesJoin = @TablesJoin + ' inner join l_device ld on ld.device_id = fp.device_id '
						
				  --if (@includeap = 1)
						--set @TablesJoin = @TablesJoin + ' inner join l_accesspoint lap on lap.ap_id = fp.ap_id '
                  
                  if (@includesession = 1)
                        set @TablesJoin = @TablesJoin + ' inner join f_session fs on fs.presence_id = fp.presence_id and ((fs.datetime_start <'+ cast(@datetimeend as nvarchar(12)) + ' and fs.datetime_end > ' + cast (@datetimestart as nvarchar(12)) + ') OR fs.datetime_start = ' +  cast (@datetimestart as nvarchar(12)) + ')'
                  
                  if (@includeappsession = 1)
                        set @TablesJoin = @TablesJoin + ' inner join f_applicationsession fapps on fapps.session_id = fs.session_id and fapps.datetime_start < ' + cast(@datetimeend as nvarchar(12)) + ' and fapps.datetime_start >= ' + cast (@datetimestart as nvarchar(12))
                        
                  if (@includeuserproperty = 1)
                        set @TablesJoin = @TablesJoin + ' inner join (' + @listuserprops + ') as lup on lup.aleuser_id = fs.user_id '

 

If I change to Dynamic Parameters . I always got this error i am not sure if is the power by  or the API i never saw anything like that

Error:API.png

 My api :

 

public string getStudentDetails(string Name,Int64? Datetimestart, Int64? Datetimeend, string Listzones, string Listaps, string Listdevices, string Listuserprops, int? Topreg)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = "Data Source=DESKTOP-R7QRMCV;Initial Catalog=DW_NSA;Integrated Security=True;TrustServerCertificate=true";
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "GetMeasures2";
            sqlCmd.Connection = myConnection;
            sqlCmd.Parameters.AddWithValue("@Name", Name);
            sqlCmd.Parameters.AddWithValue("@datetimestart", Datetimestart);
            sqlCmd.Parameters.AddWithValue("@datetimeend", Datetimeend );
            sqlCmd.Parameters.AddWithValue("@listzones", Listzones);
            sqlCmd.Parameters.AddWithValue("@listaps", Listaps);
            sqlCmd.Parameters.AddWithValue("@listdevices", Listdevices);
            sqlCmd.Parameters.AddWithValue("@listuserprops", Listuserprops);
            sqlCmd.Parameters.AddWithValue("@topreg", Topreg);
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            DataSet ds = new DataSet();
            string jsonString = string.Empty;
            myConnection.Open();
            sda.Fill(ds);
            myConnection.Close();
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                jsonString = JsonConvert.SerializeObject(ds.Tables[0]);

            }
            return jsonString;
        }

 

 

mohammedadnant
Impactful Individual
Impactful Individual

Hi @MarceloSilva5_

 

Pls use nullable in between

from

listdevices as text, listuserprops as text

to

listdevices as nullable text, listuserprops as nullable text

 

 

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

image.png

Doesn't Work 😪

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.