Merge multiple rows into one row

[jimmyfromus]

Hi, 

I'm a bit stuck on this one (which should be easy 🙂 )

I've the following table: 

Company (parentcustomerid)	Company	contactid	mandate_id	Custom	President	Chairman	District manager
5546aa33-6008-eb11-a813-000d3aaa0022	Company A	1183acbb-6808-eb11-a813-000d3aaa0022	28d65051-a826-eb11-a813-000d3aaa0022	Jim	1	0	0
763020c6-4c47-e611-b8e2-005056877b93	Company B	25faa22d-3a77-e111-8ec2-001e0bc50572	28d65051-a826-eb11-a813-000d3aaa0022	Brian	1	0	0
763e1e19-0e50-e711-aa28-005056877b93	Company C	5a911844-f681-e911-b20d-005056877b93	cabfca3d-a826-eb11-a813-000d3aaa0022	Mike	0	0	1
cd470dc6-5319-de11-8062-001e0bc50572	Company D	304c52a2-968a-e811-8833-005056877b93	28d65051-a826-eb11-a813-000d3aaa0022	John M	1	0	0
cd470dc6-5319-de11-8062-001e0bc50572	Company D	304c52a2-968a-e811-8833-005056877b93	cabfca3d-a826-eb11-a813-000d3aaa0022	John M	0	0	1
cd470dc6-5319-de11-8062-001e0bc50572	Company D	304c52a2-968a-e811-8833-005056877b93	edb6892b-a826-eb11-a813-000d3aaa0022	John M	0	1	0

And I need the following: 

Company (parentcustomerid)	Company	contactid	mandate_id	Custom	President	Chairman	District manager
5546aa33-6008-eb11-a813-000d3aaa0022	Company A	1183acbb-6808-eb11-a813-000d3aaa0022	28d65051-a826-eb11-a813-000d3aaa0022	Jim	1	0	0
763020c6-4c47-e611-b8e2-005056877b93	Company B	25faa22d-3a77-e111-8ec2-001e0bc50572	28d65051-a826-eb11-a813-000d3aaa0022	Brian	1	0	0
763e1e19-0e50-e711-aa28-005056877b93	Company C	5a911844-f681-e911-b20d-005056877b93	cabfca3d-a826-eb11-a813-000d3aaa0022	Mike	0	0	1
cd470dc6-5319-de11-8062-001e0bc50572	Company D	304c52a2-968a-e811-8833-005056877b93	28d65051-a826-eb11-a813-000d3aaa0022	John M	1	1	1

Basically just one for John M, not separated for President, Chairman and District manager values. 

Thank you for any help.  

[BA_Pete · Accepted solution]

@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

[PaulDBrown · Accepted solution]

@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.

[BA_Pete]

Hi @jimmyfromus ,

In Power Query, multi-select (hold down ctrl+click) all the fields from [Company (parentcustomerid)] to [Custom].

Then go to the Home tab on the ribbon and select 'Group By'. The fields you selected previously should be showing in the top half of the dialogue box.

In the bottom half, add an aggregation for each of your [President], [Chairman], and [District Manager] fields.

- New column name = what you want the new column to be called

- Operation = make this MAX

- Column = the column you want to perform the operation on

You will need to do this three times - once for each of your position fields.

Hit OK and this should group rows as required.

Pete

[jimmyfromus]

@BA_Pete , thanks. I seem to however end up with the same. 😞

I've also included the pbix file. 

Pbix file 

Thanks again. 

[BA_Pete]

Hi @jimmyfromus ,

Sorry, I didn't notice that your [mandate_id] field was unique to each row. I presumed it was duplicated on each row as your desired output example showed it grouped.

How do you want the [mandate_id] field handled? If it can be removed, then the grouping will work fine as I believe your other ID fields duplicate on each row. Otherwise, we will need some logic to tell Power BI which of the unique [mandate_id] values to keep when grouping.

Pete

[jimmyfromus]

Hi @BA_Pete ,

Thanks. Unfortunately I can't remove the mandate_id field. I'm using it for a relationship to another table. It's related to the 3 fields I want to group. For example: 28d65051-a826-eb11-a813-000d3aaa0022 is for President. cabfca3d-a826-eb11-a813-000d3aaa0022 for District manager etc. 

Cheers. 

[BA_Pete]

@jimmyfromus 

In that case, I'm not sure exactly how we can group these. If you can't group the [mandate_id] field, but you need to keep it, we can't group the table into a single row showing all positions for each person.

Do you just want the position fields to show a '1' on every row if that person has a '1' against that position in another row?

For example:

COMPANYID	COMPANY	CONTACTID	MANDATEID	CUSTOM	PRESIDENT	CHAIRMAN	DISTRICT
123	D	555	999	John M	1	1	1
123	D	555	888	John M	1	1	1
123	D	555	777	John M	1	1	1

Pete

Pete

[BA_Pete · Accepted solution]

@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]

Hi @BA_Pete, it's not really the result I require. I need it like in my first post, so 1 line per contact. John M has one row and not three. Thanks again for your help.  

[jimmyfromus]

@BA_Pete I guess I have to get rid of the mandateid column, right?

[BA_Pete]

Hi @jimmyfromus ,

Yes, if you want to end up with exactly the output you showed, you will need to lose the [mandate_id] field, at least in that format. It may be possible to group the different [mandate_id]'s into a single cell e.g. m_id1, m_id2, m_id3 etc., or to create a new column for each different [mandate_id], but I don't think either of these will help you with your dimension relationship.

Pete