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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
abhishekharde
New Member

Merge two rows (having a different cell value) into a single row; overwrite values for text

Hello,

 

I want to merge rows when duplicate "Task" value is found; and overwrite text from one "Response" value to another cellwhen Scaled Value is "-1.00". For example, see below two rows:

InstanceAssigned toTaken onTaskResponseScaled valueAssignment groupComments and Work notes
ABC123Nick12-Mar-21 03:31:15ANC0691115Very Satisfied4.00ElectricalCreated from a similar incident INC0690512

ABC123Nick12-Mar-21 03:31:15ANC0691115Nothing, easy going and uncomplicated. Thank you very much!-1.00ElectricalCreated from a similar incident INC0690512

 

Once the rows are merged, it should look like below: 

InstanceAssigned toTaken onTaskResponseScaled valueAssignment groupComments and Work notes
ABC123Nick12-Mar-21 03:31:15ANC0691115Nothing, easy going and uncomplicated. Thank you very much!4.00Electrical

Created from a similar incident INC0690512

 

Can you please suggest what DAX formula/ Power Query shall be used? Thank you so much in advance!

6 REPLIES 6
Anonymous
Not applicable

Hi @abhishekharde ,

In fact, @VahidDM 's answer is roughly correct. Just a little modification is needed. Please refer to the following.

 

 

Table 2 = 
FILTER(SUMMARIZE(
    'Table',
    'Table'[Ask],
    'Table'[Date],
    'Table'[Item],
    'Table'[No.],
    'Table'[Number],
    'Table'[User ID],
    "Response",
        CALCULATE(
            MAX( 'Table'[Reply] ),
            ALL( 'Table' ),
            'Table'[Value] = -1
        ),
    "Scale Value", MAX( 'Table'[Value] )
),[Scale Value]<>-1)

 

 

vpollymsft_0-1639734262812.png

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi @abhishekharde ,

It seems the value"-1" was replaced by "4.00". How can I get the condition? Please provide more data and expected output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous , thanks for your note.  Please refer to the dataset in the this Excel as you asked. :https://docs.google.com/spreadsheets/d/1_wL7RtP2pXXJZiKD0U37iOtqKSXETYdp/edit?usp=sharing&ouid=113008550954619586830&rtpof=true&sd=true 

 

Essentially, when there are duplicate Numbers (ex. TKT0536285), I would like the output record with below conditions:

1. those two rows with duplicate Numbers shall be merged into a single row ,and

2. "-1.00" Value of one row's record, shall be replaced with the other value (ex. 4.00) in that merged single row, and

3. the merged record's Reply column shall display text for Ask = Inputs.

 

For Example,  for below duplicate records of the dataset: 

ItemNo.User IDDateNumberAskReplyValue
SurveyABCDE0630219Hir2/8/2021TKT0536285InputsIt was Ok -1.00
SurveyABCDE0630219Hir2/8/2021TKT0536285ScoreVery Satisfied4.00

 

The output should be:

ItemNo.User IDDateNumberAskReplyValue
SurveyABCDE0630219Hir2/8/2021TKT0536285InputsIt was Ok 4.00

 

Could you please help? 

 

Thanks in advance!

abhishekharde
New Member

@VahidDM - Thanks for replying, but the DAX measure does not seem to be working.  

Hi @abhishekharde 

 

It's not a measure, you need to use it to create a new table with dax codes.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

VahidDM
Super User
Super User

Hi @abhishekharde 

 

Try this DAX code to add a new table:

Table 2 =
SUMMARIZE(
    'Table',
    'Table'[Instance],
    'Table'[Assigned to],
    'Table'[Taken on],
    'Table'[Task],
    'Table'[Assignment group],
    'Table'[Comments and Work notes],
    "Response",
        CALCULATE(
            MAX( 'Table'[Response] ),
            ALL( 'Table' ),
            'Table'[Scaled value] = -1
        ),
    "Scale Value", MAX( 'Table'[Scaled value] )
)

 

Output:

 

VahidDM_0-1637395616389.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.