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

[abhishekharde]

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:

Instance	Assigned to	Taken on	Task	Response	Scaled value	Assignment group	Comments and Work notes
ABC123	Nick	12-Mar-21 03:31:15	ANC0691115	Very Satisfied	4.00	Electrical	Created from a similar incident INC0690512
ABC123	Nick	12-Mar-21 03:31:15	ANC0691115	Nothing, easy going and uncomplicated. Thank you very much!	-1.00	Electrical	Created from a similar incident INC0690512

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

Instance	Assigned to	Taken on	Task	Response	Scaled value	Assignment group	Comments and Work notes
ABC123	Nick	12-Mar-21 03:31:15	ANC0691115	Nothing, easy going and uncomplicated. Thank you very much!	4.00	Electrical	Created from a similar incident INC0690512

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

[v-rongtiep-msft]

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)

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-rongtiep-msft]

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.

[abhishekharde]

Hi @v-rongtiep-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: 

Item	No.	User ID	Date	Number	Ask	Reply	Value
Survey	ABCDE0630219	Hir	2/8/2021	TKT0536285	Inputs	It was Ok	-1.00
Survey	ABCDE0630219	Hir	2/8/2021	TKT0536285	Score	Very Satisfied	4.00

The output should be:

Item	No.	User ID	Date	Number	Ask	Reply	Value
Survey	ABCDE0630219	Hir	2/8/2021	TKT0536285	Inputs	It was Ok	4.00

Could you please help? 

Thanks in advance!

[abhishekharde]

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

[VahidDM]

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]

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:

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/