topic Re: Expression.Error: We cannot apply operator <= to types List and List. in Power Query

Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Wed, 16 Feb 2022 23:14:51 GMT

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]

https://docs.google.com/spreadsheets/d/1acDcIKuLpEdmdRbfEn_tWfUgUzmWc5Km/edit?usp=sharing&ouid=110421848725922329650&rtpof=true&sd=true

Re: Expression.Error: We cannot apply operator <= to types List and List.

HotChilli — Wed, 16 Feb 2022 23:38:01 GMT

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?

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Thu, 17 Feb 2022 02:32:33 GMT

Yeah, we know what the error means, but we can't fix it for you until we see

the expression that is causing the error. You are probably intending to refer to a value <= another value, but are instead using syntax that is causing a list to be compared to another list.

--Nate

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Thu, 17 Feb 2022 04:05:23 GMT

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
Custom1

This 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.

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Thu, 17 Feb 2022 04:06:30 GMT

Anonymous

I have shared some more details below.
Hope this can help you all members in forum to guide me.

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Thu, 17 Feb 2022 07:32:29 GMT

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

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Thu, 17 Feb 2022 09:05:49 GMT

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"

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Tue, 22 Feb 2022 02:09:11 GMT

Hi Anonymous ,

Could you tell me if your problem has been solved based on Anonymous ?

If it is, kindly Accept it as the solution. More people will benefit from it. Thanks in advance !

Best Regards,
Eyelyn Qin

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Tue, 22 Feb 2022 17:48:46 GMT

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.

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Tue, 22 Feb 2022 17:50:32 GMT

Anonymous

Hi,
This worked with sample data, but the logic I need to build for my work with orginal data is volatile and I want to test the solution to the orginal data and then confirm if this is exact solution.

Thanks for following up.

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Fri, 25 Feb 2022 20:29:05 GMT

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 HotChilli

https://docs.google.com/spreadsheets/d/1hsK7UnC8DqhLskYWCZyHhalyP9lYpsdP/edit?usp=sharing&ouid=110421848725922329650&rtpof=true&sd=true

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Sat, 26 Feb 2022 16:55:22 GMT

I bet it's the Account # field causing trouble. It makes it look like escape clauses (#"Account #"). Try renaming it Account Number.

--Nate

Re: Expression.Error: We cannot apply operator <= to types List and List.

Anonymous — Mon, 28 Feb 2022 08:31:12 GMT

Hi 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.

Sample-edited.xlsx