Forum Discussion
Custom Column - For each ID where [Column_1] = X, return Y where [Column_2] = A or B
Hi there,
In the table below I want to do something like this:
"For each [Case_ID] where [Metric] = "Comment", return [Score] where [Metric] = "Satisfaction (INC)" or "Satisfaction (REQ)"
In other words: I want the yellow values to appear within the rows where the orange values appear. I woul really prefer not to pivot the table, as it would potentially ruin the relations between the tables in my model.
Any advice?
Thanks!
| Case_ID | Response | Metric | Date | Score | Desired_Outcome |
| INC0348 | Comment | 26-08-2022 | -1 | 100 | |
| INC0348 | Highly agrees | Treatment | 26-08-2022 | 100 | |
| INC0348 | Highly agrees | Follow-up | 26-08-2022 | 100 | |
| INC0348 | Very satisfied | Satisfaction (INC) | 26-08-2022 | 100 | |
| INC0348 | N/A | Communication | 26-08-2022 | -1 | |
| INC366 | It was ok I guess… | Comment | 26-08-2022 | -1 | 50 |
| INC366 | Agrees | Treatment | 26-08-2022 | 75 | |
| INC366 | Agrees | Follow-up | 26-08-2022 | 75 | |
| INC366 | Highly agrees | Communication | 26-08-2022 | 100 | |
| INC366 | Very satisfied | Satisfaction (INC) | 26-08-2022 | 50 | |
| REQ475 | Great! A+ | Comment | 26-08-2022 | -1 | 100 |
| REQ475 | Highly agrees | Treatment | 26-08-2022 | 100 | |
| REQ475 | Highly agrees | Follow-up | 26-08-2022 | 100 | |
| REQ475 | Highly agrees | Communication | 26-08-2022 | 100 | |
| REQ475 | Very satisfied | Satisfaction (REQ) | 26-08-2022 | 100 |
6 Replies
You can add a custom column with this code. Modify "Previous Step name" accordingly.
if [Metric] = "Comment" then Table.SelectRows(#"Previous Step name", (x)=> x[Case_ID] = [Case_ID] and ( x[Metric] = "Satisfaction (INC)" or x[Metric] = "Satisfaction (REQ)"))[Score]{0} else nullHere is the full code of a query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRzNjA2sVDSUVIAYuf83NzUvBIgy8hM18BC18jAyAjI0TUEEoYGBkqxOsg6PDLTM3IqFRLTi1JTi4H8kKLUxBJs+kFaQRbg1+6Wn5OTX65bWkCc9rDUokqF4sSSzOK0zNQUoEAwmJ2YXJKZn6egAVSoSZxBfvqOUK+X5mUmJ4K0Yw0AuD5jMzMg17NEoTyxWCE/W8FTIb00tbj4UcMyAkFoaoBqhCPBkDM3xbTYkWCAYdOFHtp4/YsWUBATyAhwU4QxQa6BJmB3uYP8qqjgqE1keoNrJCu54dRNVGrDqZvo0IObQCD0gOpwJ9dYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case_ID = _t, Response = _t, Metric = _t, Date = _t, Score = _t, Desired_Outcome = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case_ID", type text}, {"Response", type text}, {"Metric", type text}, {"Date", type text}, {"Score", Int64.Type}, {"Desired_Outcome", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Metric] = "Comment" then Table.SelectRows(#"Changed Type", (x)=> x[Case_ID] = [Case_ID] and ( x[Metric] = "Satisfaction (INC)" or x[Metric] = "Satisfaction (REQ)"))[Score]{0} else null) in #"Added Custom"Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Hi v-jingzhang
Your suggestion actually works! However, in practice it seems to only work on a small number of rows. Maybe its due to the way the function iterates over the rows, idk. Anyway, the columns in my table had not finished loading after 10 minutes, so I am afraid that the calculation is too demanding to be used in the actual report.
Thank you for your suggestion after all.
Kind regards Magnus
Yes, the iterating function is not efficient enough. I thought of another solution by using merging. See if it can run faster.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRzNjA2sVDSUVIAYuf83NzUvBIgy8hM18BC18jAyAjI0TUEEoYGBkqxOsg6PDLTM3IqFRLTi1JTi4H8kKLUxBJs+kFaQRbg1+6Wn5OTX65bWkCc9rDUokqF4sSSzOK0zNQUoEAwmJ2YXJKZn6egAVSoSZxBfvqOUK+X5mUmJ4K0Yw0AuD5jMzMg17NEoTyxWCE/W8FTIb00tbj4UcMyAkFoaoBqhCPBkDM3xbTYkWCAYdOFHtp4/YsWUBATyAhwU4QxQa6BJmB3uYP8qqjgqE1keoNrJCu54dRNVGrDqZvo0IObQCD0gOpwJ9dYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case_ID = _t, Response = _t, Metric = _t, Date = _t, Score = _t, Desired_Outcome = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case_ID", type text}, {"Response", type text}, {"Metric", type text}, {"Date", type text}, {"Score", Int64.Type}, {"Desired_Outcome", Int64.Type}}), #"Filter Rows" = Table.SelectRows(#"Changed Type", each [Metric] = "Satisfaction (INC)" or [Metric] = "Satisfaction (REQ)"), #"Added Custom" = Table.AddColumn(#"Filter Rows", "Metric_Comment", each "Comment"), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Case_ID", "Metric"}, #"Added Custom", {"Case_ID", "Metric_Comment"}, "Output", JoinKind.LeftOuter), #"Expanded Output" = Table.ExpandTableColumn(#"Merged Queries", "Output", {"Score"}, {"Score.1"}) in #"Expanded Output"Merging is happening on two steps in the same query.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
if the values in the column of Metric is unique for each ID, then this code may work.
=let a=Table.Buffer(Table.Group(PreviousStepName,"Case_ID",{"n",each _})) in Table.AddColumn(PreviousStepName,"Desired_Outcome",each let b=a{[Case_ID=[Case_ID]]}? in if [Metric]<>"Comment" or b=null then null else b{[Metric="Satisfaction (INC)"]}?[Score]? ??b{[Metric="Satisfaction (REQ)"]}?[Score]?)
Hi wdx223_Daniel
Thank you for your reply.The question marks you have posted in your code is not accepted in the editor.
Should they be another symbol instead?
Kind regards Magnus
