Combining data based on additional keys

[rmorris]

Hi,

I'm working with a SQL database that i only have read only access to. I've got two data table that i've imported to PowerBI that i need combine based the SalesID and the CommentID, but each SalesID can have either zero to 10+ CommentID's each. I'm trying to combine the text in the comment field into one cell.

Table 1

SalesID	Value
M255487	$500.00
M255488	$650.00
M255489	$150.00

Table 2

SalesID	CommentID	Comment
M255487	1	15/07 spoke to Kevin about order
M255487	2	Emailed Quote
M255487	3	Rang and spoke to Peter
M255488	1	Cash Sale

What i'm trying to achieve as the end result, where it will combine the comments into one field

SalesID	Value	Comment
M255487	$500.00	15/07 spoke to Kevin about order, Emailed Quote, Rang and spoke to Peter
M255488	$650.00	Cash Sale
M255489	$150.00

[SamInogic · Accepted solution]

Hi @rmorris ,

We have created two sample tables “Sample Table 1” and “Sample Table 2” as you explained your requirement and you want a combined table where comments need to be displayed as comma separated data based on Unique Sales ID.

Please refer to the below screenshot for sample data.

Now we need to create a Merged table, so Navigate to Edit Query and click on Merge Queries as New option as shown in the below screenshot.

This will create a Merged Table automatically, now close the Query editor and create a Column in the Merged Table with below DAX expression:

Comments = CONCATENATEX(FILTER('Sample Table 2', 'Sample Table 2'[SalesID] = Merge1[SalesID]), 'Sample Table 2'[Comment], ",")

Please refer to the below screenshot for Comments DAX and Output for the same.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[FreemanZ · Accepted solution]

hi @rmorris 

try to add a calculated column in table1 like:

Comment = 
VAR _sales = [SalesID]
RETURN
CONCATENATEX(
    CALCULATETABLE(
        VALUES(Table2[Comment]),
        Table2[SalesID] = _sales
    ),
    Table2[Comment],", "
)

it worked like:

[SamInogic · Accepted solution]

Hi @rmorris ,

We have created two sample tables “Sample Table 1” and “Sample Table 2” as you explained your requirement and you want a combined table where comments need to be displayed as comma separated data based on Unique Sales ID.

Please refer to the below screenshot for sample data.

Now we need to create a Merged table, so Navigate to Edit Query and click on Merge Queries as New option as shown in the below screenshot.

This will create a Merged Table automatically, now close the Query editor and create a Column in the Merged Table with below DAX expression:

Comments = CONCATENATEX(FILTER('Sample Table 2', 'Sample Table 2'[SalesID] = Merge1[SalesID]), 'Sample Table 2'[Comment], ",")

Please refer to the below screenshot for Comments DAX and Output for the same.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/