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 ajay-sformula,
I reproduce your scenario and get expected results, please review the following steps.
1. Click "New Table" umder Modeling on Home page, type the formulas below, you will create two new tables: TableA and TableB.
TableA = SELECTCOLUMNS(FILTER(Test,Test[S or C Type]="S"),"Person Id",Test[Person Id],"Element Id",Test[Element Id],"S Value",Test[Value])
TableA
TableB = SELECTCOLUMNS(FILTER(Test,Test[S or C Type]="C"),"Person Id",Test[Person Id],"C value",Test[Value])
TableB
2. In TableA, create a calculated column using the formula below, you will get expected result.
C value = LOOKUPVALUE(TableB[C value],TableB[Person Id],TableA[Person Id])
result table
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
