Forum Discussion
Helper IIExpression.Error: We cannot convert a value of type List to type Number.
Super UserHi 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
Helper II
= 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.