Power Query - Merge Creating Multiple Rows Where 2 Records Exist

[karmaimages]

Hi,

I'm using power query to looking some values against an external data source of user accounts.

So where I have for example

Name	Manager
Joe Bloggs	(Lookup Value)

If the user has 2 or more accounts as they're been recreated for example

Display Name	Account Name	Manager
Joe Bloggs	Joe Bloggs	Jane Doe
Joe Bloggs	Joe Bloggs2	Jane Doe
Joe Bloggs	Joe Bloggs3	Jane Doe

Where I'm linking on Name and Display Name to gather the manager name I'm getting the result as

Name	Manager
Joe Bloggs	Jane Doe
Joe Bloggs
Joe Bloggs

This is messing up some counts that are needed for reports as it's creating necessary rows, how do I get Power Query to only add the one row not create multiple for the same person?

 

[johnny51981]

I'm not quite understanding what you are looking for as a final result, but I think you are wanting to see just 1 Joe Bloggs with 1 Jane Doe. So in order to do that, you would do a Group By with your Display Name and Manager as the Groupings, and then a Count of Rows as an Aggregation, which for this example, I have named as "Count of Accounts"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Display Name", type text}, {"Account Name", type text}, {"Manager", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Display Name", "Manager"}, {{"Count of Accounts", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[GraH]

After merging add a custom column and add something like

extData_[manager]

that should return the manager field from the first record of the merged external data table.
Mind M is case sensitive.