Forum Discussion
convert 2 rows into two columns with common value
Hi,
I have the following table:
Person Id S or C Type Element Id Value
00001 S 1 3
00001 C 1 Blue is my fav
00002 S 2 1
00002 C 2 I like red
00003 S 3 2
00003 C 3 I want purple
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
00002 2 1 I like red
00003 3 2 I want purple
Every person in my data has two rows, one with S type and another with C type.
Can anyone advise how i do it in either power query or otherwise in DAX ?
many thanks for your help.
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
5 Replies
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,
AngeliaHi,
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
Hi ajay-sformula,
Another easier way to get expected result using Power Query, please review the following.
In query editor, please select Element Id->pivot column->Value and Don't aggregate as the screenshot shown.
When you click "OK", you will get expected result.
result
The following is my query statement.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgACQyUdpWAgBtHGSrE6CFFnqKhTTmmqQmaxQm6lQlpiGVyJEVSjEVgZsqgzVNRTISczO1WhKDUFLm0M1WQMVoIs6gwV9VQoT8wrUSgoLSrISVWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Person Id" = _t, #"S or C Type" = _t, #"Element Id" = _t, Value = _t]), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"S or C Type"]), "S or C Type", "Value") in #"Pivoted Column"
Best Regards,
Angelia
