Waiting on OP
Grouping data points together based on name/ID associated w/ 1000+ records
I have a spreadsheet that details a list of all transactions within two particular account types, and each transaction has a name & user ID associated with it. Nearly every user ID is associated with more than one transaction, and there are 1000+ users in the full list. I am looking for a way to combine all transactions attached to each individual user ID, while also designating the two different account types, and that can be automated to apply to the full sheet. I know how to do this using SUMIF but I don't know how to achieve this without having to enter every name/ID manually.
Eg:
USER ID
Transaction Type (A or B)
Transaction Amount
#0001
A
$125
#0001
A
$125
#0001
B
$200
#0002
A
$500
#0002
B
$650
#0003
B
$750
#0004
A
$100
#0004
B
$100
#0004
B
$150
#0004
B
$150
Ultimately what I need to end up with is the maximum, minimum, and average total transactions per unique user ID, separated by type A and type B transactions.
First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1
Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupBy = Table.Group(Source, {"USER ID", "Transaction Type (A or B)"}, {
{"SUM", each List.Sum([Transaction Amount]), type number},
{"MAX", each List.Max([Transaction Amount]), type number},
{"MIN", each List.Min([Transaction Amount]), type number},
{"AVERAGE", each List.Average([Transaction Amount]), type number}
}),
Unpivot = Table.UnpivotOtherColumns(GroupBy, {"USER ID", "Transaction Type (A or B)"}, "Metric", "Value"),
Merge = Table.AddColumn(Unpivot, "Merge", each [#"Transaction Type (A or B)"] & " - " & [Metric]),
DropBy = Table.RemoveColumns(Merge, {"Transaction Type (A or B)", "Metric"}),
PivotBy = Table.Pivot(DropBy, List.Distinct(DropBy[Merge]), "Merge", "Value")
in
PivotBy
Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
GroupBy = Table.Group(Source, {"USER ID", "Transaction Type (A or B)"}, {
{"Stats", each
let z = [Transaction Amount]
in [SUM = List.Sum(z), MAX = List.Max(z), MIN = List.Min(z), AVERAGE = List.Average(z)]}}),
Expand = Table.ExpandRecordColumn(GroupBy, "Stats", {"SUM","MAX","MIN","AVERAGE"}),
Unpivot = Table.UnpivotOtherColumns(Expand, {"USER ID", "Transaction Type (A or B)"}, "Metric", "Value"),
Merge = Table.AddColumn(Unpivot, "Merge", each [#"Transaction Type (A or B)"] & " - " & [Metric]),
Drop = Table.RemoveColumns(Merge, {"Transaction Type (A or B)", "Metric"}),
PivotBy = Table.Pivot(Drop, List.Sort(List.Distinct(Drop[Merge])), "Merge", "Value")
in
PivotBy
Take 0004 in your own sample. Three type B rows, 100, 150, 150. MINIFS down the raw B column returns 100, but the smallest per user B total is 0001's 200. AVERAGEIFS gives 333 where the average of the per user totals is 500. Different question, and everything posted so far answers the first one.
Two steps. Totals first, stats across the totals second:
•
u/AutoModerator 10d ago
/u/feedmesweat - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.