cancel
Showing results for 
Search instead for 
Did you mean: 
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
v-polly-msft
Community Support
Community Support

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.

 

v-polly-msft
Community Support
Community Support

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 @v-polly-msft , 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=11300...

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors