
Active Member

February 17, 2022

Hi All,
I am brand new member of this forum and also an apprentice in PQ- M language.
I need help to build two custom columns :
1) Sku Count
2) New Customer Flag
Please see the attached sample file fo reference. Until I learnt about PQ, I have been deriving the above two using Excel Countifs formulas.
There is a reference date which is a constant for this year, but may change next year.
I tried the below for deriving SKU Count but got the below error:
= Table.Group(Source,"Account-Zip-Sku",{{"Sku Count", each if [Invoice Dates] <= [Start of Month] then "0" else "1"}})
Expression.Error: We cannot apply operator <= to types List and List.
Details:
Operator=<=
Left=[List]
Right=[List]
If you have a much stable query I can learn or depend on to achieve the two outcomes please share.
Thank you!

Active Member

February 17, 2022

Continuing some more details on my original post.
While my post was getting verified, I did some research and performed these two queries to achieve the two custom columns I have mentioned above.
Steps I followed
- Uploaded data into Power Query
- I referenced my orginal PQTest query which I loaded to PQ.
- then wrote the below in advanced editor:
let
Source = PQTest,
Custom1 = Table.Group(RawData_iDigSales,"Account-Zip-Sku",{{"Sku Count", each if [Invoice Dates] = [Start of Month] then "0" else "1"}})
in
Custom1
From the above Helper Query I derived a partial or incorrect Sku Count.
This is where I hit the road block : I wanted to use the query
let
Source = PQTest,
Custom1 = Table.Group(RawData_iDigSales,"Account-Zip-Sku",{{"Sku Count", each if [Invoice Dates] <= [Start of Month] then "0" else "1"}})
in
Custom1
But PQ threw an error as below when I include "<=" expression in the query.
Expression.Error: We cannot apply operator <= to types List and List.
Details:
Operator=<=
Left=[List]
Right=[List]
The above is Problem number 1
Yet I continued becuase I am hoping to find the solution to problem number 1.
4. Then I merged the above helper query with the PQ Test query as a new table called it Merge 1
5. Using the Merge 1, I referenced the query to derive the next custom column ="New Account"
6. Used the below query in advanced editor:
let
_reference_date = #date(2021,01,01),
Source = Merge1,
// #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sku Count", Int64.Type}})
Custom1 = Table.Group(Source,"Account-Zip-Sku",{{"New Account",each if List.Min([Sku Count]) = 0 and List.Min([Invoice Date]) >= _reference_date then "Y" else"N"}})
in
Custom1
Now my request to all is, is there a way to fix the "<=" expression error and is there a more agile way to derive these custom columns instead of the long process I have here?
Note: Although I am grouping, I still need to retain all rows from the orginal table.
Thank you for reading and your help.
1 Guest(s)
