Column value of Unknown, Converted to know Areas and duplicated values.

[Anonymous]

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  |

[ryan_mayu · Accepted solution]

@Anonymous 

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

[Anonymous]

Thanks so much, your tb1 solution opened my eyes up to what I really needed.

[ryan_mayu]

@Anonymous 

you are welcome.