Expression.Error: We cannot convert a value of type List to type Number.

[FarmerKenny]

I am failing to pass a List of numbers (defined as data type decimal number) to a parameter of the same data type in a query.  This List is passed to a stored procedure as it's second parameter, ClientIDList,  like this: 

= dbo_Service_Enrollment(StartDate,ClientIDList, null, null, null)

 and it raises this error message: 

Expression.Error: We cannot convert a value of type List to type Number.

I have tried to resolve this error by converting the ClientIDList to a number using Number.From(ClientIDList) , but PowerBI responds with: 

DataFormat.Error: We couldn't convert to Number.

How can I solve this error? 

[PhilipTreacy]

Hi @FarmerKenny 

In Power Query a List is a specific data type.  You can't have a list that is a number, but you can have a list of numbers.

How are you creating this list?

If you do something like 

ClientIDList = {101010, 202020, 303030}

You get a list of numbers.  In this case whole numbers.  Which leads me to ask, why, if your Client ID's are whole numbers you say you have created a List of numbers (defined as data type decimal number) ?

So if you have a list, ClientIDList, and you try to pass that into something which is expecting a single number, you will get an error.  Likewise, calling Number.From(ClientIDList) will generate an error.  You can't convert a list of numbers to a single value.

You say When I create the list of ClientID's it is converted behind the scenes by PowerBI as Text

How is this happening?  If you create the list as I showed above, you get a list of numbers.  How/when is it being converted to text?

Regards

Phil

[FarmerKenny]

= Sql.Database("Hostname", "DatabaseName", [Query="WITH AllRows AS (#(lf)select#(lf)                    LedgerType,#(lf)                    FamilyAcct,#(lf)                    ClientID,#(lf)                    CreatedDate,#(lf)                    Charge_Amount,#(lf)                    [Service],#(lf)                    PaymentsToDate,#(lf)                    Balance#(lf)                   #(lf)                from ECCOVIA.dbo.[Account_LedgerByFamily] #(lf)where  LedgerType in ('Charge', 'Payment') #(lf)and CAST(CreatedDate as DATE)  >= '"&StartTime&"' #(lf)--group by LedgerType, FamilyAcct, ClientID#(lf))#(lf)   #(lf)select #(lf)           #(lf)        LedgerType,#(lf)        FamilyAcct,#(lf)        ClientID,      #(lf)        MAX(CAST(CreatedDate as DATE))as CreatedDate,#(lf)        Charge_Amount,#(lf)        [Service],#(lf)        PaymentsToDate,#(lf)        Balance#(lf)      #(lf)from#(lf)#(lf)      AllRows#(lf)#(lf)where NOT EXISTS (#(lf)select * from ECCOVIA.dbo.[Account_LedgerByFamily] b#(lf)where b.ClientID = AllRows.ClientID#(lf)And LedgerType = 'Payment'#(lf)and CAST (CreatedDate as DATE)  >= '"&StartTime&"' #(lf))#(lf)#(lf)group by LedgerType, FamilyAcct, ClientID,Charge_Amount,[Service], PaymentsToDate, Balance#(lf)order by FamilyAcct, ClientID desc#(lf);"])

Phil, I was unable to get my Parameter List,  ClientIDList (Decimal Number)  which is a select ClientID (INT datatype) from the source table to allow me to use it in my data source query. So, I gave up on that.  Seems like they should allow Parameter Lists to be whole numbers and it may have worked.  I was able to get a Date Parameter, StartTime,  to be referenced in my data source query because both the Parameter and the CreatedDate column were both datatype dates.  

[PhilipTreacy]

Hi @FarmerKenny 

So the 2nd parameter is supposed to be a number?  If so, how can you pass multipe numbers (in a list) as the parameter?

If the list contains a single number then you can pass that in using ClientIDList{0} where the {} 'extracts' the first item in the list.

Regards

Phil

[FarmerKenny]

Yes, the second parameter here is list of ID's that happen to be defined as INT in  SQL.  When I create the list of ClientID's it is converted behind the scenes by PowerBI as Text. Not sure what to do here. It must be datatype Number to join against the other tables in my model.  The ClientID field is 6 characters of numbers.