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.
Good Afternoon All,
I hope this is not to difficult and I'm horrible with explaining exactly what I need help with so I hope the tables below will shed some light. I have a table given to me and a recod can come in as a 'unknown' area. For each record of a 'unknown' area, I would like to duplicate that four times and replace the unknown area with the four know areas. See examples below.
Thanks in advance.
Table Given
| Value1 | Value2 | Area |
|--------|---------|-----------|
| 12 | James | Unknown |
| 14 | Eric | Unknown |
| 10 | Steve | UpTown |
| 8 | Sasha | LeftTown |
| 6 | Tom | RightTown |
| 19 | Jake | DownTown |
Table I need to Convert To
| Value1 | Value2 | Area |
|--------|---------|-----------|
| 12 | James | UpTown |
| 12 | James | LeftTown |
| 12 | James | RightTown |
| 12 | James | DownTown |
| 14 | Eric | UpTown |
| 14 | Eric | LeftTown |
| 14 | Eric | RightTown |
| 14 | Eric | DownTown |
| 10 | Steve | UpTown |
| 8 | Sasha | LeftTown |
| 6 | Tom | RightTown |
| 19 | Jake | DownTown |
Solved! Go to Solution.
you can try to create a new table
Table 2 =
VAR tb1=SELECTCOLUMNS(FILTER('Table','Table'[Area]="Unknown"),"Value1",'Table'[Value1],"Value2",'Table'[Value2])
VAR tbl2=FILTER(DISTINCT('Table'[Area]),'Table'[Area]<>"Unknown")
VAR tbl3= SELECTCOLUMNS(CROSSJOIN(tbl2,tb1),"Value1",[Value1],"Value2",[Value2],"Area",'Table'[Area])
return UNION(FILTER('Table','Table'[Area]<>"Unknown"),tbl3)
please see the attachment below
Proud to be a Super User!
you can try to create a new table
Table 2 =
VAR tb1=SELECTCOLUMNS(FILTER('Table','Table'[Area]="Unknown"),"Value1",'Table'[Value1],"Value2",'Table'[Value2])
VAR tbl2=FILTER(DISTINCT('Table'[Area]),'Table'[Area]<>"Unknown")
VAR tbl3= SELECTCOLUMNS(CROSSJOIN(tbl2,tb1),"Value1",[Value1],"Value2",[Value2],"Area",'Table'[Area])
return UNION(FILTER('Table','Table'[Area]<>"Unknown"),tbl3)
please see the attachment below
Proud to be a Super User!
Thanks so much, your tb1 solution opened my eyes up to what I really needed.
you are welcome.
Proud to be a Super User!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |