Power Query Workout 02 - Extract Customers with max. Sales

[Matthias]

Difficulty Rating:

Introduction:

Last week we combined data from sources with different headers (try it out if you haven’t done it yet).
This week we are going to extract from this data for each week the customer with the highest sales in each location.

Your task:
Build a dynamic solution which is independent from the number of locations and the number of weeks in the data.
This is a beginner - intermediate workout => You can build a UI driven/supported solution.

(NOTE: For more advanced users, attempt to minimize the number of manual changes using relevant M code functions)

2 Extract Customers with max Sales.xlsx (17.4 KB)

This workout was posted on Monday April 04, 2023, and the author’s solution will be posted on Sunday April 10, 2023.

[borydobon]

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
Pivot = Table.Pivot(Table.Group(Table.UnpivotOtherColumns(Source,List.FirstN(Table.ColumnNames(Source),3), “cw”, “V”),
{“cw”,“Location”}, {“C”, each Table.Max(_,“V”)[Customer]}),List.Distinct(Source[Location]), “Location”, “C”)
in
Pivot

[Luan]

let
    Fonte = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    rem = Table.UnpivotOtherColumns(Fonte, {"Location", "Customer"}, "Atributo", "Valor"),
    filt = Table.SelectRows(rem, each ([Atributo] <> "Customer Nr.")),
    gp = Table.Group(filt, {"Atributo","Location"}, {{"Contagem", each 
    [
    a = _,
    b = Table.Max(Table.Sort(a, {"Valor",Order.Descending}),"Valor")
    ][b]
    }})[[Contagem]],
    exp = Table.ExpandRecordColumn(gp, "Contagem", {"Location", "Customer", "Atributo"}),
    res = Table.Pivot(exp, List.Distinct(exp[Location]), "Location", "Customer")
in
    res

[guptabhavya2502]

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Location", "Customer", "Customer Nr."}, "Week", "Sales"),
    Grouped = Table.Group(Unpivot, {"Location", "Week"}, {{"All", each Table.Max(_, "Sales")[Customer]}}),
    ExpectedOutput = Table.ExpandRecordColumn(Table.Group(Grouped, {"Week"}, {{"All", each Record.FromList([All],[Location])}}), "All", List.Distinct(Source[Location]))
in
    ExpectedOutput

[BrianJ]

Oh, man! The big hitters from the nightly Excel BI challenge have started showing up.

Welcome, @guptabhavya2502 , @Luan and @borydobon!

Workout participants - In additon to @Matthias, some other serious folks to learn from here…

[BrianJ]

@Matthias ,

Fun challenge. Here’s my solution:

let
    Source = Table.RemoveColumns( Excel.CurrentWorkbook(){[Name="Data"]}[Content], "Customer Nr."),
    UnpivotOtherCols = Table.UnpivotOtherColumns(Source, {"Location", "Customer"}, "cw", "Value"),
    Group = Table.Group(UnpivotOtherCols, {"cw", "Location"}, {{"All", each _, type table [Location=text, Customer=text, Attribute=text, Value=number]}, {"MaxGroup", each List.Max([Value]), type number}}),
    Expand = Table.RemoveColumns( Table.SelectRows( Table.ExpandTableColumn(Group, "All", {"Customer", "Value"}, {"Customer", "Value"}), each [Value] = [MaxGroup] ), {"Value", "MaxGroup"}),
    PivotLocation = Table.Pivot(Expand, List.Distinct(Expand[Location]), "Location", "Customer")
in
    PivotLocation

[valeriabreveglieri]

Hello, here’s how I solved it. Thanks for the nice challenge!!!

let
    Source = Excel.Workbook(File.Contents("MyPath\2 Extract Customers with max Sales.xlsx"), null, true){[Item="Data",Kind="Table"]}[Data],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location", "Customer", "Customer Nr."}, "cw", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Location", "cw"}, {{"Max Sales", each List.Max([Value]), type number}, {"All", each _, type table [Location=nullable text, Customer=nullable text, #"Customer Nr."=nullable text, Attribute=text, Value=number]}}),
    #"Add Ranking" = Table.TransformColumns( #"Grouped Rows",{{"All", each Table.AddRankColumn(_,"Rank",{"Value",Order.Descending})}}),
    #"Select Rank=1" = Table.TransformColumns(#"Add Ranking",{{"All",each Table.SelectRows(_,each ([Rank] = 1))}}),
    #"Extract Customer" = Table.AddColumn(#"Select Rank=1", "Customer", each [All][Customer]{0})[[cw],[Location],[Customer]],
    #"Pivoted Column" = Table.Pivot(#"Extract Customer", List.Distinct(#"Extract Customer"[Location]), "Location", "Customer")
in
    #"Pivoted Column"

[Anurag]

Hi ,

My solution for this workout:

let
    Source = Excel.Workbook(File.Contents("E:\WorkOut\PowerQuery\2 Extract Customers with max Sales.xlsx"), null, true),
    Data_Table = Source{[Item="Data",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Table,{{"Location", type text}, {"Customer", type text}, {"Customer Nr.", type text}, {"cw01", Int64.Type}, {"cw02", Int64.Type}, {"cw03", Int64.Type}, {"cw04", Int64.Type}, {"cw05", Int64.Type}, {"cw06", Int64.Type}, {"cw07", Int64.Type}, {"cw08", Int64.Type}, {"cw09", Int64.Type}, {"cw10", Int64.Type}, {"cw11", Int64.Type}, {"cw12", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location", "Customer", "Customer Nr."}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "CW"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Location", "CW"}, {{"Count", each _, type table [Location=nullable text, Customer=nullable text, #"Customer Nr."=nullable text, CW=text, Value=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "maxsale", each let

a= Table.Max([Count],"Value")
in 
a),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"maxsale"}),
    #"Expanded maxsale" = Table.ExpandRecordColumn(#"Removed Other Columns", "maxsale", {"Location", "Customer", "CW"}, {"Location", "Customer", "CW"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded maxsale", List.Distinct(#"Expanded maxsale"[Location]), "Location", "Customer"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"CW", type text}, {"NY", type text}, {"TKY", type text}})
in
    #"Changed Type1"

PowerQuery2.pbix (24.6 KB)

Thanks

[HermioneGranger1176]

Here is one way I will do it in Power Query.

let
	Source  = Excel.CurrentWorkbook(){[ Name = "Data" ]}[Content],
	Remove  = Table.RemoveColumns ( Source, { "Customer Nr." } ),
	Unpivot = Table.UnpivotOtherColumns ( Remove, { "Location", "Customer" }, "Cw", "Sales" ),
	Group   = Table.Group (
		Unpivot,
		{ "Cw", "Location" },
		{ "All", each Table.Max ( _, "Sales" )[Customer] }
	),
	Return  = Table.Pivot ( Group, List.Distinct ( Group[Location] ), "Location", "All" )
in
	Return

2 Extract Customers with max Sales.xlsx (27.0 KB)

[KimC]

@Matthias thanks for the challenge, here is my solution, Not as eloquent as some but it got the job done:

[graeme.mccumiskey]

@Matthias Thanks for another great challenge. Had to learn how to use a new function (Table.Max).

OUtput

let
Source = Excel.CurrentWorkbook(){[Name = “Data”]}[Content],
// Unpivot data table to allow grouping
Unpivot = Table.UnpivotOtherColumns(
Source,
{“Location”, “Customer”, “Customer Nr.”},
“Week”,
“Sales”
),
// Group Data by location & Week
GroupLocNWk = Table.Group(
Unpivot,
{“Location”, “Week”},
{
{
“AllData”,
each _,
type table [
Location = text,
Customer = text,
#“Customer Nr.” = text,
Week = text,
Sales = number
]
}
}
),
// Add Column to extract the Max Value od Sales by Location and Week
MaxSalesRecord = Table.AddColumn(GroupLocNWk, “Custom”, each Table.Max([AllData], “Sales”)),
// Select Customer name from record
ExtractCustomerWithMaxSales = Table.ExpandRecordColumn(
MaxSalesRecord,
“Custom”,
{“Customer”},
{“Custom.Customer”}
),
// Remove unrequired columns
CleanUpCols = Table.RemoveColumns(ExtractCustomerWithMaxSales, {“AllData”}),
// Pivot Data into output format
PivotData = Table.Pivot(
CleanUpCols,
List.Distinct(CleanUpCols[Location]),
“Location”,
“Custom.Customer”
)
in
PivotData

[Pavel.Adam]

Good one, thanks. I got the results using List.* based code, see Approach1. I rethinked it and get another solution, shorter in code, see Approach2.

2 Extract Customers with max Sales PavelAdam.xlsx (19.3 KB)

Approach1
let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
CleanUpData = Table.RemoveColumns(Source, {“Customer Nr.”}),
distinctLocations = List.Distinct(Source[Location]),
columnNames2Proceed = List.Buffer(List.Skip(Table.ColumnNames(CleanUpData), 2)),
CustomGroupOutput = Table.Combine(
Table.Group(
CleanUpData,
{“Location”},
{
{
“AllRows”,
(subTbl) =>
[
CustomerNames = subTbl[Customer],
MaxValueIndices = List.Transform(
columnNames2Proceed,
(actColumnName) =>
[
actColumnValues = Table.Column(subTbl, actColumnName),
MaxValue = List.Max(actColumnValues),
MaxValueIndex = List.PositionOf(actColumnValues, MaxValue)
][MaxValueIndex]
),
MaxValueCustomerNames = List.Transform(
MaxValueIndices,
(actIndex) => CustomerNames{actIndex}
),
subTblOutput = Table.FromColumns(
{
columnNames2Proceed,
MaxValueCustomerNames,
List.Repeat({subTbl[Location]{0}}, List.Count(columnNames2Proceed))
},
{“CW”, “CustomerName”, “Location”}
)
][subTblOutput]
}
}
)[AllRows]
),
Unpivot = Table.Pivot(CustomGroupOutput, distinctLocations, “Location”, “CustomerName”)
in
Unpivot

Approach2
let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
step0_uniqueLocations = List.Distinct(Source[Location]),
step1_CleanAndUnpivot = Table.UnpivotOtherColumns(
Table.RemoveColumns(Source, {“Customer Nr.”}),
{“Location”, “Customer”},
“cw”,
“Value”
),
step2_GetMaxValueCustomerName = Table.Group(
step1_CleanAndUnpivot,
{“cw”, “Location”},
{“CustomerName”, (subTbl) => Table.Max(subTbl, “Value”)[Customer]}
),
step3_PivotToFinalOutput = Table.Pivot(
step2_GetMaxValueCustomerName,
step0_uniqueLocations,
“Location”,
“CustomerName”
)
in
step3_PivotToFinalOutput

[Pavel.Adam]

@borydobon

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
Pivot = Table.Pivot(Table.Group(Table.UnpivotOtherColumns(Source,List.FirstN(Table.ColumnNames(Source),3), “cw”, “V”),
{“cw”,“Location”}, {“C”, each Table.Max(_,“V”)[Customer]}),List.Distinct(Source[Location]), “Location”, “C”)
in
Pivot

Very nice approach @borydobon . Clear, easy to understand. Thanks for sharing!

[BrianJ]

@Pavel.Adam ,

Good to see you here! Always enjoy your approaches and perspectives.

• Brian

[AlexisOlson]

Since Table.Max is so popular I thought I’d add a different approach that I’ve written about before.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, List.FirstN(Table.ColumnNames(Source),3), "Week", "Value"),
    GroupBy = Table.Group(Unpivot, {"Location", "Week"}, {{"MaxValue", each List.Max([Value]), type number}}),
    SelfMerge = Table.NestedJoin(GroupBy, {"Location", "Week", "MaxValue"}, Unpivot, {"Location", "Week", "Value"}, "Grouped Rows", JoinKind.LeftOuter),
    ExpandCustomer = Table.ExpandTableColumn(SelfMerge, "Grouped Rows", {"Customer"}, {"Customer"}),
    RemoveColumn = Table.RemoveColumns(ExpandCustomer,{"MaxValue"}),
    PivotLocation = Table.Pivot(RemoveColumn, List.Distinct(RemoveColumn[Location]), "Location", "Customer")
in
    PivotLocation

[RafalM]

Hello everyone

Here’s my solution to this task.

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
UnpivotWeeks = Table.UnpivotOtherColumns(Source, {“Location”, “Customer”, “Customer Nr.” }, “Weeks”, “Sales”)
GroupByCustomers = Table.Group(UnpivotWeeks, {“Weeks”, “Location”},
{
{“Customers”, each Table.Max(_, “Sales”)[Customer] }
} ),
PivotLocationColumn = Table.Pivot(GroupByCustomers, List.Distinct(GroupByCustomers[Location]), “Location”, “Customers”)
in
PivotLocationColumn

[Matthias]

Hello,
thanks to everyone who participated and everyone who solved the workout is a winner!

This week many approaches were using unpivoting, grouping and Table.Max. I’ll present here an alternative approach without grouping and Table.Max, using sorting and remove duplicates instead.

1 - I too start off with Unpivoted Other Columns to create a dynamic solution: Mark the first 3 columns before going to the Transform tab to select Unpivot Columns > Unpivot Other Columns
You can change Attribute to cw in the formula bar, to save one renaming step.
2 - Next sort cw ascending and Value descending.
3 - #“Removed Other Columns” gets rid of the Customer Nr. and Value columns and more importantly it makes sure that the sorting from the sorting step sticks. Without this step you would need to add either alternative steps like adding and deleting an Index column or add a Table.Buffer.
4 - Mark the Location and cw column and select on the Home tab Remove Rows > Remove Duplicates
=> By this only the top customer for each cw and location will remain
5 - Mark the Location column and on the Transform tab select Pivot Columns. As Values Column take Customer and under Advanced Options select Don’t Aggregate

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Location", "Customer", "Customer Nr."}, "cw", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"cw", Order.Ascending}, {"Value", Order.Descending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Location", "Customer", "cw"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Location", "cw"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Location]), "Location", "Customer")
in
    #"Pivoted Column"

What I like about it is that it is almost 100% UI based, with the only exception of the cw name change in the first step. And although it is beginner orientated, I think it is also a bit surprising because of the potential not sticking of the sorting step.

Try it out, but make sure to compare it also to the other solutions, following completely different approaches! => Hope you learn something!

Big thanks to everyone who has blured or hidden the details! I have taken this now out so that it’s easier to compare solutions.

[donnclark]

Donn Clark
Apr 11, 2023

Here’s my solution … learning and catching up.

Thanks

[AlejandroSimon]

Espero que no sea tarde, asi que aquí adjunto mi query…

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {“Location”, “Customer”, “Customer Nr.”}, “cw”, “Value”),
Group = Table.Group(Unpivot, {“Location”, “cw”}, {{“Count”, each Record.Field(Table.Max(_, {“Value”}), “Customer”)}}),
Pivot = Table.Pivot(Group, List.Distinct(Group[Location]), “Location”, “Count”)
in
Pivot

[willow.lang]

let
Source = Excel.CurrentWorkbook(){[Name=“Data”]}[Content],
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(Source, {“Location”, “Customer”, “Customer Nr.”}, “Week”, “Value”),
#“Grouped Rows” =
Table.Group(
#“Unpivoted Other Columns”,
{“Location”, “Week”},
{
{“Detail”,
each let
maxSale = List.Max([Value]),
Filteredtable =
Table.SelectColumns(
Table.SelectRows(_,each [Value]=maxSale),
“Customer”)[Customer]{0}
in
Filteredtable,
type text
}
}),
#“Pivoted Column” =
Table.Pivot(#“Grouped Rows”, List.Distinct(#“Grouped Rows”[Location]), “Location”, “Detail”)
in
#“Pivoted Column”