Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to add a column and set its value based on whether or not a value exists in a different table.
I have a table called Tasks which I want to add a column called WorkItemType. The value of this column will be a text entry that is defined by whether or not the parent of the row in tasks is in a different table.
So I have:
Tasks
WorkItemId | ParentWorkItemId |
123 | 122 |
124 | 135 |
129 | 151 |
Bugs
WorkItemId | Title |
122 | A |
128 | B |
User Stories
WorkItemId | Title |
138 | Support01 |
135 | Feature01 |
151 | Feature02 |
And would like the result to be:
Tasks
WorkItemId | ParentWorkItemId | WorkItemType |
123 | 122 | Bug |
124 | 138 | Support |
129 | 151 | Other |
The relationship structure looks like this:
In order to break it down into smaller chunks I have tried to get just the bug determination working. I have tried to following
= Table.AddColumn(Custom1, "WorkItemType", each if( Table.Contains (Bugs, Bugs[WorkItemId] = [ParentWorkItemId])) then "bug" else "other" )
But I get an error stating that:
Expression.Error: We cannot convert the value false to type Record.
Details:
Value=FALSE
Type=[Type]
I have also tried:
= Table.AddColumn(Custom1, "WorkItemType", each if( Table.Contains (Bugs, Bugs[WorkItemId] = Tasks[ParentWorkItemId])) then "bug" else "other" )
But that ends up with an error: Expression.Error: A cyclic reference was encountered during evaluation.
Any help would be greatly appreciated.
Solved! Go to Solution.
Hi @BBurnett ,
You could try the following steps:
Step 1,create a new table base on User Stories:
User Stories2 = CALCULATETABLE('User Stories',FILTER(all('User Stories'),'User Stories'[Title]="Support01"))
Step 2, use the following measure :
final =
VAR test1 =
CONTAINS ( 'Bugs', Bugs[WorkItemId], MAX ( Tasks[ParentWorkItemId] ) )
VAR test2 =
IF ( test1 = TRUE, "Bugs", "Others" )
VAR test3 =
CONTAINS (
'User Stories2',
'User Stories2'[WorkItemId], MAX ( Tasks[ParentWorkItemId] )
)
VAR test4 =
IF ( test3 = TRUE, "Support", "Others" )
VAR test5 =
IF ( TEST2 = "Bugs", "Bugs", IF ( test4 = "Support", "Support", "Others" ) )
RETURN
test5
Step3 ,new column base on the measure:
final1 = [final]
Wish it is helpful for you!
Best Regards
Lucien
Hi @BBurnett ,
You could try the following steps:
Step 1,create a new table base on User Stories:
User Stories2 = CALCULATETABLE('User Stories',FILTER(all('User Stories'),'User Stories'[Title]="Support01"))
Step 2, use the following measure :
final =
VAR test1 =
CONTAINS ( 'Bugs', Bugs[WorkItemId], MAX ( Tasks[ParentWorkItemId] ) )
VAR test2 =
IF ( test1 = TRUE, "Bugs", "Others" )
VAR test3 =
CONTAINS (
'User Stories2',
'User Stories2'[WorkItemId], MAX ( Tasks[ParentWorkItemId] )
)
VAR test4 =
IF ( test3 = TRUE, "Support", "Others" )
VAR test5 =
IF ( TEST2 = "Bugs", "Bugs", IF ( test4 = "Support", "Support", "Others" ) )
RETURN
test5
Step3 ,new column base on the measure:
final1 = [final]
Wish it is helpful for you!
Best Regards
Lucien
Yes they are both the same data type. All integers.
@BBurnett , Are these of same datatype
Bugs[WorkItemId] = [ParentWorkItemId] ?
Also check this
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |