Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
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.
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:
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!
@VahidDM - Thanks for replying, but the DAX measure does not seem to be working.
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/
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/
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
27 | |
23 | |
22 | |
22 |