Hi,
I'm trying to understand the last product category bought by inactive customers and their demographics. I grouped the transactions so that each customer ID (called ContactKey) was grouped by their product categories (called LottEventCategory) and I used All Rows to expand out the demographics I need to keep like ApproxAge, Sex, AscribeToCode and CompanyCode.
However, when I went to use Group By a second time, by the last product category (LottEventCategory) that they bought, the demographic fields disappear.
How do I amend the syntax in the advanced editor so that I keep those fields in my second Group By action? Please see attached. Thank you.
Not sure why you need a second grouping, my guess is you're not on the right direction. Grouping by 2 fields and keeping all the rows, then expanding a column from All Rows table, will in fact do an ungrouping (new rows will show up in the result), it's the same thing as grouping by 3 fields instead of 2.
Grouping, ungrouping and the grouping again might not be the best idea. Please prepare a sample data table with some relevant records, and a manual example of the expected outcome, this way we will be able to identify a solution easier.
Hi Catalin,
Thanks for your reply. Based on your advice, I'm trying to group once and include All Rows at the same time. Then use the double-headed arrow to expand out the extra fields that I need.
Unfortunately I get this error message Expression.SyntaxError: Token Comma expected. Would you be able to help cast another pair of eyes over this? Thank you.
I have attached some dummy data. The expected output is:
Group By ContactKey and LottEvent Category. Then List.Last LottEventCategory and sum the Totals belonging to that Last LottEventCategory. For example, Customer number/ ContactKey number 11 will have the last LottEventCategory = Raffle and Total = $115, Last FinTrxDate = 13/04/2018 and list their last ApproxAge, Sex, AscribeToTypeCode, CompanyCode.
Hi Linda,
Why 11-Ruffle should be 115$? I know you mentioned last event, and that is Ruffle. But when you filter the table for 11-Ruffle, you will get 4 rows totaling 145, not 115. The first 2 rows are also 11-Ruffle, how do you remove these, based on what criteria?
Hi Catalin,
My mistake, it was a typo. The expected output is customer number 11 Raffle $145. Thank you!
The expected output is: Group By ContactKey and LottEvent Category. Then List.Last LottEventCategory and sum the Totals belonging to that Last LottEventCategory. For example, Customer number/ ContactKey number 11 will have the last LottEventCategory = Raffle and Total = $145, Last FinTrxDate = 13/04/2018 and list their last ApproxAge, Sex, AscribeToTypeCode, CompanyCode.
Linda
Hi Catalin,
I have also attached the advanced editor code for my first product category. Because I have grouped rows twice, could you please cast a second pair of eyes to check that I have done it correctly? Thank you.
Hi Linda,
Don't think you even need grouping.
Please test the attached query, you have examples for how to find the last event for each Key, sum the totals for last event, select the Age-Sex columns for each key.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ContactKey", Int64.Type}, {"LottEventCategory", type text}, {"ApproxAge", Int64.Type}, {"Sex", type text}, {"AscribeToTypeCode", type text}, {"CompanyCode", type text}, {"FixTrxDate", type datetime}, {"Total", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"FixTrxDate", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", (x)=> Table.SelectRows(#"Sorted Rows", each _[ContactKey]=x[ContactKey])),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"ContactKey"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"ContactKey", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "Last Event", each List.Last([Custom][LottEventCategory])),
#"Added Custom3" = Table.AddColumn(#"Added Custom1", "Last Event Total", (x)=> List.Sum(Table.SelectRows(x[Custom], each _[ContactKey] = x[ContactKey] and _[LottEventCategory] = x[Last Event])[Total])),
#"Added Custom2" = Table.AddColumn(#"Added Custom3", "Custom.2", each Table.SelectColumns([Custom],{"Sex","ApproxAge"}))
in
#"Added Custom2"
Hi Catalin,
Thanks for your reply. I've tested the query suggested but it has been running for 6 hours and is still going. I'll give it till tomorrow if it doesn't time out before that. Would Group By be more efficient? Thanks
Do you have many records?
Try using Table.Buffer in the steps just before Add Column steps.
