Forum Discussion
Custom column to create a new customer flag using group by function.
Alex_T
I wrote this logic for Sku Count based on your solution for New Customer Flag above. Please see below query:
= Table.Group(Source,"Account-Zip-Sku",{{"Sku Count", each if List.Dates[Invoice Dates] <= List.Dates[Start of Month] then "0" else "1"}})
But it throws me this error :
Expression.Error: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=Invoice Dates
Notes: I referenced the orginal source and created a reference query to test and derive Sku Count using the above logic.
If I remove the List. Dates function then it throws this error:
Expression.Error: We cannot apply operator <= to types List and List.
Details:
Operator=<=
Left=[List]
Right=[List]
Could you please help me write the correct query?
Thank you!
Hiya Anonymous ,
Table.Group requires aggregations. [Invoice Dates] is referrring to the column as a column in the first error and as a list in the second error, in both cases it is trying to make sense of the expression, which unfortunately doesn't follow the right syntax
List.Dates(start as date, count as number, step as duration) as list
List.Dates creates a sequential list of dates, at best using [Invoice Dates], if the syntax was right, would offer a start date but this would fail straight away as it's a list and not a scalar value, your syntax is List.Dates[Invoice Dates], not List.Dates(<parameters>)
TBH, I have never used Table.Groups before but it appeared to produce the right result for you for New Customer, in this case I think you want a count and Table.Groups may again be the right approach (in which case I think you may want to use List.Min again with the dates, but I need to be sure I understand what you are trying to achieve). In the example PQ_Test, all the Invoice Dates are >= Start of Month so it looks like that is poor test data as it contradicts your <= comparison (or at least will work poorly with your test).
So this is a bit confusing (I also have no previous experience of what SKUs are! I work with hospital data!), can you clarify, is this right...
- for each Account-Zip-Sku, count the number of Invoice Dates that are the same or earlier than the Start of Month
I'm not sure it is and if not could you re-write it and clarify what you need and we can have another go 🙂
Cheers
PS this is perhaps closer to what you appeared to be trying to do so may be what you want...
Table.Group(Source,"Unique ID",{ "SKU_count", each
if List.Min([Invoice Date]) <= List.Min([Start of month]) then 1 else 0})
Also, these are good references for learning PowerQuery...
Power Query M function reference - PowerQuery M | Microsoft Docs
Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo
Hi Alex_T
I appreciate you taking time to help.
I am attaching a new sample data close to my orginal file (due to confidentiality can't share the OG file).I build pivot table based reports using csv and excel files for CPG industry, SKU is item in terms of supply chain.
Here's what I am trying to achieve. I get data sources from different databases and I am trying to perform all the ETL using power query and automate the refresh as each week fresh transactions are posted.
I have been in a fix to derive these two columns:- Sku Count - based on the unique ID (Account-Zip-Sku) I need to count the number of times this combination is repeated in my data set keeping the logic that if the combination has repeated more than once when the invoice date is less than the start of the month. If true ,0 else 1.
Why its designed this way is becuase : - New Account :client wants to filter the data based on month number and see how many new accounts are derived each month with reference date in connection.
If the Sku count is >0 then "Y" else"N" --> Logic set for New Account.
The samples I am able to share here doesn't contain previous two years data but my original file does. that's why we are considering, reference date.
We are using the logic Invoice date<= Start of the month - becuase the client needs to set filters at month level.I hope there is an agile way I can derive these in the same table as two new columns?
Can you please look into this and let me know?
Note: I tried the logicTable.Group(Source,"Unique ID",{ "SKU_count", each if List.Min([Invoice Date]) <= List.Min([Start of month]) then 1 else 0})
But I got this error
Cheers,
V- Sku Count - based on the unique ID (Account-Zip-Sku) I need to count the number of times this combination is repeated in my data set keeping the logic that if the combination has repeated more than once when the invoice date is less than the start of the month. If true ,0 else 1.