Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rmorris
Frequent Visitor

Combining data based on additional keys

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

SalesIDValue
M255487$500.00
M255488$650.00
M255489$150.00

 

Table 2

SalesIDCommentIDComment
M255487115/07 spoke to Kevin about order
M2554872Emailed Quote
M2554873Rang and spoke to Peter
M2554881Cash Sale

 

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

SalesIDValueComment
M255487$500.0015/07 spoke to Kevin about order, Emailed Quote, Rang and spoke to Peter
M255488$650.00Cash Sale
M255489$150.00 

 

2 ACCEPTED SOLUTIONS
SamInogic
Super User
Super User

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.

SamInogic_0-1676982058125.png


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.

SamInogic_1-1676982094413.png


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.

SamInogic_2-1676982113434.png


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/

View solution in original post

FreemanZ
Super User
Super User

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:

FreemanZ_0-1676995047153.png

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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:

FreemanZ_0-1676995047153.png

 

SamInogic
Super User
Super User

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.

SamInogic_0-1676982058125.png


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.

SamInogic_1-1676982094413.png


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.

SamInogic_2-1676982113434.png


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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors