Forum Discussion
convert 2 rows into two columns with common value
Hi ajay-sformula,
Each Person Id can have many "Element Ids", you just add a "Element Ids" in TableB, then lookup Cvalue based on "Element Ids" and "Person Id".
1. Create TableA and TableB.TableA = SELECTCOLUMNS(FILTER(Test1,Test1[S or C Type]="S"),"Person Id",Test1[Person Id],"Element Id",Test1[Element Id],"S Value",Test1[Value]) TableB = SELECTCOLUMNS(FILTER(Test1,Test1[S or C Type]="C"),"Person Id",Test1[Person Id],"Element Id",Test1[Element Id],"C value",Test1[Value])
2. In TableA, create a calculated column using the formula and get expected result.C value = LOOKUPVALUE(TableB[C value],TableB[Person Id],TableA[Person Id],TableB[Element Id],TableA[Element Id])
In addition, for second solution in Query Editor, please click both "S or C Type" and "Element Id"->Pivot column->value and don't aggregate, you will get right results.
Best Regards,
Angelia
Hi,
THANK YOU FOR YOUR HELP - it is greatly appreciated.
I tried the method above of a New Table TABLE A and TABLE. B .. it did not work.... i believe it did not work, because i did not explain the data in entirety in my example, ... Each Person Id can have many "Element Ids" ,,, so below is a better version of the data i have and what i want to achieve ... is there a way to do it ?
Person Id S or C Type Element Id Value
00001 S 1 3
00001 C 1 Blue is my fav
00001 S 2 1
00001 C 2 I want a car
00001 S 3 2
00001 C 3 I like TV
00002 S 1 1
00002 C 1 I like red
00002 S 2 1
00002 C 2 I want some food
00002 S 3 2
00002 C 3 I like netflix
00003 S 1 2
00003 C 1 I want purple
00003 S 2 1
00003 C 2 I want a holiday
00003 S 3 4
00003 C 3 I like movies
i want to convert this to a table with following format:
Person Id Element Id S Value C Value
00001 1 3 Blue is my fav
00001 2 1 I want a car
00001 3 2 I likle TV
00002 1 1 I like red
00002 2 1 I want some food
00002 3 2 I likle netflix
00003 1 2 I want purple
00003 2 1 I want a holiday
00003 3 4 I like movies
Thank you for your advice.
Hi ajay-sformula,
Each Person Id can have many "Element Ids", you just add a "Element Ids" in TableB, then lookup Cvalue based on "Element Ids" and "Person Id".
1. Create TableA and TableB.
TableA = SELECTCOLUMNS(FILTER(Test1,Test1[S or C Type]="S"),"Person Id",Test1[Person Id],"Element Id",Test1[Element Id],"S Value",Test1[Value]) TableB = SELECTCOLUMNS(FILTER(Test1,Test1[S or C Type]="C"),"Person Id",Test1[Person Id],"Element Id",Test1[Element Id],"C value",Test1[Value])
2. In TableA, create a calculated column using the formula and get expected result.
C value = LOOKUPVALUE(TableB[C value],TableB[Person Id],TableA[Person Id],TableB[Element Id],TableA[Element Id])
In addition, for second solution in Query Editor, please click both "S or C Type" and "Element Id"->Pivot column->value and don't aggregate, you will get right results.
Best Regards,
Angelia