distinct count power query

[ajm]

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:

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.

 

[jorismoerings]

Hi,

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

#"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:

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