Extracting records for max sales per product...

[Matty]

Hi Team,

I'm looking for help with Power Query to achieve the following...

Here's my data:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Type[/TH]
[TH]Sales[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Bag[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Box[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Bag[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Bunch[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bag[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Box[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I want to extract the records for each Product where the biggest Sales have taken place. The result should be as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Type[/TH]
[TH]Sales[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Bag[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Bag[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Bunch[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bag[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help me with this? To reiterate: I would like M code for this if possible, not DAX.

Thanks,

Matty

 

[ParamRay]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // Set the table name on this line
    #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"All Rows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Max Row", each Table.Max([All Rows], "Sales")),
    #"Expanded Max Row" = Table.ExpandRecordColumn(#"Added Custom", "Max Row", {"Type", "Sales"}, {"Type", "Sales"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Max Row",{"Product", "Type", "Sales"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Type", type text}, {"Sales", type number}, {"Product", type text}})
in
    #"Changed Type"

 

[DRSteele]

I am no PQ expert, to be sure. But this seems to have worked. My Table is named Table3. I used the Group feature with two aggregations, one of which was any function (I chose Median) applied to the text field "Type", which simply reports that text as text.

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Type", type text}, {"Sales", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Type", each List.Median([Type]), type text}, {"Sales", each List.Max([Sales]), type number}})
in
#"Grouped Rows"

 

[Matty]

Thanks, guys. Both work as desired.

Cheers,

Matty

 

[Matty]

Hi,

Next issue: to show where data inconsistencies exist.

Data as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Type[/TH]
[TH]Shop[/TH]
[TH]Sales[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Bag[/TD]
[TD]A[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Box[/TD]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Bag[/TD]
[TD]A[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Bunch[/TD]
[TD]A[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bag[/TD]
[TD]A[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Bag[/TD]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I want to show only those Products where the Type differs across different Shops. The resulting table should look as follows:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Type[/TH]
[TH]Shop[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Bag[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Box[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

Only Apple shows on this table because all of the other Products have consistent Types (Apple has two different Types across different Shops). Pear, on the other hand, doesn't show because there is only one record. Orange also doesn't show because Bag is consistent in both Shops.

Hope this is clear and someone can help. Again, using M code if possible.

Cheers,

Matty

 

[Matty]

Never mind - managed to work up a solution to the above issue by grouping up twice.

Cheers,

Matty