Merge multiple rows into one row

jimmyfromus ,

 

Here's how to do my suggestion above, if that will work.

 

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZG9TkMxDEZfpbpzLTl/ju9Iy4TUJ6g6OIkrKtRbxMbb44CoUEuhHWCwZCk5zvGX9XpIKZJICECIDFqcA2EXABFbEBFE74f5sDzsn2V6nd1Z7xwHqaUA8U+I50YJUz/0dPnaw27fZ1rhe23m6yFTQI+VINaYQcnQwuoNtYHEOZcxfJFa9NfSVsT7BkGyIc4Q1toRp1iqcfkGqcXLTqbvtNSpGwE1IWjusHi+pLW0PsloccUIW2IHOvZNPLZTpErZVgntF63V7kmPRt2uW9UWMzYLKwVTa9o3Rzrb/NPq3vqAsSYvHkZiAeWOcAinVtf+4OFxmq3O0vo7ryvTOnr9V17aCvHoyy1eH5ltNm8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompanyID = _t, Company = _t, contactid = _t, mandate_id = _t, Custom = _t, President = _t, Chairman = _t, #"District manager" = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"CompanyID", type text}, {"Company", type text}, {"contactid", type text}, {"mandate_id", type text}, {"Custom", type text}, {"President", Int64.Type}, {"Chairman", Int64.Type}, {"District manager", Int64.Type}}),
    groupPerson = Table.Group(chgAllTypes, {"Custom"}, {{"data", each _, type table [CompanyID=nullable text, Company=nullable text, contactid=nullable text, mandate_id=nullable text, Custom=nullable text, President=nullable number, Chairman=nullable number, District manager=nullable number]}}),
    addCalcPresident = Table.AddColumn(groupPerson, "calcPresident", each Table.Max([data], "President")),
    expandCalcPresident = Table.ExpandRecordColumn(addCalcPresident, "calcPresident", {"President"}, {"President"}),
    addCalcChairman = Table.AddColumn(expandCalcPresident, "calcChairman", each Table.Max([data], "Chairman")),
    expandCalcChairman = Table.ExpandRecordColumn(addCalcChairman, "calcChairman", {"Chairman"}, {"Chairman"}),
    addCalcDistrictManager = Table.AddColumn(expandCalcChairman, "calcDistrictManager", each Table.Max([data], "District manager")),
    expandCalcDistrictManager = Table.ExpandRecordColumn(addCalcDistrictManager, "calcDistrictManager", {"District manager"}, {"District manager"}),
    expandDataGroup = Table.ExpandTableColumn(expandCalcDistrictManager, "data", {"CompanyID", "Company", "contactid", "mandate_id"}, {"CompanyID", "Company", "contactid", "mandate_id"})
in
    expandDataGroup

 

This gives me the following output:

 

 

Pete

jimmyfromus 

If you want to keep the field for mandate_id, which one is to be shown for John since there are 3 distinct values attributed to him?

This is as far as I've got.

First the model. (I've created a Dim job table to keep the job order in the visual)

 

1) Unpivot the last 3 columns (President, Chairman and District Manager columns) in Power Query to get this:

 2) create a measure for the Sum of Value and then another to group by the name:

Grouped by Name =
CALCULATE (
    [Sum Value],
    ALLEXCEPT (
        'DataTable',
        'DataTable'[Company (parentcustomerid)],
        'DataTable'[Company],
        'DataTable'[contactid],
        'DataTable'[Job]
    )
)

 

Create a matrix visual with the "Dim job [job]" column as the columns and add the [Grouped by name] and you get this:

 

We are getting 3 rows for John because there are 3 distinct mandate-Ids attributed to him.