distinct count power query

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
hi all, i am having trouble getting a distinct count with power query. i have merged two tables. first column machine part names, second is part numbers related to these text descriptions. instead of getting a distinct count of the part numbers, i keep getting a count of the rows containing the part numbers.

this is my query:

Power Query:
let
    Source = Table.NestedJoin(Table1,{"Maintenance Item Description (REFINED)"},Table3,{"Maintenance Item Description (REFINED)"},"Table3",JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Maintenance Item Code"}, {"Table3.Maintenance Item Code"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table3", {"Maintenance Item Description (REFINED)"}, {{"Count", each Table.RowCount(_), type number}, {"Maintenance Item Code", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Maintenance Item Code.1", each Table.Column([Maintenance Item Code],"Maintenance Item Code")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Maintenance Item Code.1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Maint Item List", each Table.Column([Maintenance Item Code],"Table3.Maintenance Item Code")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Maint Item List", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text})
in
    #"Extracted Values"

can anyone suggest how my grou can return distinct part numbers not the count of rows.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi,

Assuming the gouping is at tyhe correct level, try this:

Power Query:
#"Grouped Rows" = Table.Group(#"Expanded Table3", {"Maintenance Item Description (REFINED)"}, {{"Count", each Table.RowCount(_), type number}, {"Maintenance Item Code", each _, type table}}),

change this line into:
Power Query:
#"Grouped Rows" = Table.Group(#"Expanded Table3", {"Maintenance Item Description (REFINED)"}, {{"Distinct Count", each Table.RowCount(_), type number}, {"Maintenance Item Code", each _, type table}}),
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top