Forum Discussion
Expression.Error: We cannot apply operator <= to types List and List.
Hi All,
I need guidance on how to fix this above error in a custom query.
Expression.Error: We cannot apply operator <= to types List and List.
Details:
Operator=<=
Left=[List]
Right=[List]
12 Replies
HotChilli
Please see the below.let
Source = PQTest,Custom1 = Table.Group(Source,"Account-Zip-Sku",{{"Sku Count", each if [Invoice Dates] <= [Start of Month] then "0" else "1"}})
in
Custom1This is the first query - loaded from the sample table. Then I referenced the query to PQ test(2) for which I applied the steps aove in adavanced editor.
Hi, Anonymous,
to build on what Anonymous suggested, you need to "discover the wonders of the All Rows option in the aggregation".
Here is one way how you can achieve what you want to do:
let Source = PQTest, #"Grouped Rows" = Table.Group(Source, {"Account-Zip-Sku"}, {{"Grouped Table", each _, type table [Invoice Dates=nullable date, #"Account #"=nullable number, State=nullable text, Zip Code=nullable text, Item Name ID=nullable number, Start of Month=nullable date, #"Account -Zip"=nullable text, #"Account-Zip-Sku"=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([Grouped Table], "Comparison", each if [Invoice Dates] <= [Start of Month] then 0 else 1, Int64.Type)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Invoice Dates", "Start of Month", "Comparison"}, {"Invoice Dates", "Start of Month", "Comparison"}) in #"Expanded Custom"Hi Anonymous
This is very close to what I am looking for. I appreciate your help thank you.
I would like to accept this solution but I am testing this with my orginal data sample this week, then I can confirm if this is the solution.
I learnt a lot with the above query you shared, PQ is so powerful.
Anonymous - Yes, I have been using the aggregate option in grouping rows. But I am not very comfortable or polished at writing queries yet, working my way. Appreciate your help too.Hi Anonymous
I tried the query you shared above with my original data and it shows the below error.
DataFormat.Error: Invalid cell value '#VALUE!'.
This is my query in advanced editor.
let
Source = AllSalesData,
#"Grouped Rows" = Table.Group(Source, {"Account-Zip-Sku"}, {{"Grouped Table", each _, type table [Invoice Dates=nullable date,
Retail Accounts=nullable text, Address=nullable text, City=nullable text, #"Account #"=nullable number,
State=nullable text, Zip Code=nullable text, Phone=nullable number, Chain Status=nullable text, OnOff Premises=nullable text,
Brands=nullable text, Item Names=nullable text, Item Name ID=nullable number, Sales Teams=nullable text,
Sales Depletions 9L Cases=nullable number, Category=nullable text, Account=nullable text, Full Account=nullable text,
#"City-State"=text, Chain Group=nullable text,Region=nullable text, Year=number, Month=number, Start of Month=date, OnOff Premise Map=nullable text,Data Source=nullable text ,#"Account-Zip-Sku"=text] }}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([Grouped Table], "Comparison", each if [Invoice Dates] <= [Start of Month] then 1 else 0, Int64.Type)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Invoice Dates", "Start of Month", "Comparison"}, {"Invoice Dates", "Start of Month", "Comparison"}),
#"Expanded Grouped Table" = Table.ExpandTableColumn(#"Expanded Custom", "Grouped Table", {"Retail Accounts", "Address", "City", "Account #", "State", "Zip Code", "Phone", "Chain Status", "OnOff Premises",
"Brands", "Item Names", "Item Name ID", "Sales Teams", "Sales Depletions 9L Cases", "Category","Account","Full Account", "City-State", "Chain Group", "Region","Year", "Month","OnOff Premise Map","Data Source"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Grouped Table",{{"Account-Zip-Sku", type text}, {"Retail Accounts", type text}, {"Address", type text}, {"City", type text}, {"Account #", Int64.Type}, {"State", type text},
{"Zip Code", Int64.Type}, {"Phone", Int64.Type}, {"Chain Status", type text}, {"OnOff Premises", type text}, {"Brands", type text}, {"Item Names", type text}, {"Item Name ID", Int64.Type}, {"Sales Teams", type text},
{"Sales Depletions 9L Cases", type number}, {"Region", type text}, {"City-State", type text}, {"Category", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Account", type text},{"Full Account", type text},
{"Chain Group", type text},{"OnOff Premise Map", type text},{"Data Source", type text}, {"Invoice Dates", type date}, {"Start of Month", type date}, {"Comparison", Int64.Type}})
in
#"Changed Type"What I am trying to achieve is the same.
1)Sku Count in a custom column
2) New Customer or account flag using the Sku Count logic.
Attaching my orginal data file after removing confidential data.
Please help.
Anonymous Anonymous HotChilliHi Anonymous,
I do not see the error you mentioned after I have downloaded your sample data and used your query.
I could only see this error in Account # column: "DataFormat.Error: We couldn't convert to Number."
This was because in the last step, Changed Type, you have this:
{"Account #", Int64.Type}while you should have this
{"Account #", type text}The error you have mentioned: DataFormat.Error: Invalid cell value '#VALUE!'
could indicate that there are some error values in your full data set. You would need to handle them first (either by ignoring, replacing or removing them).
In the file below I have used your query with the above adjustment and I can see no errors.
I see that the data is attached but what are the steps of your power query transforms?
Can you post the Advanced Editor code please?
Ok I think I see what you are aiming for. Rather than use a condition in that step, I would add the custom column first. Then when you group by Account-ZIP-SKU, you will already have the ones and zeros to do with whatever you'd like, whether count, contains, or even just making an "if any on that Account-ZIP-SKU are 1" after you have your tables grouped--that is, if you've discovered the wonders of the All Rows option in the aggregation.
--Nate
