convert 2 rows into two columns with common value

[ajay-sformula]

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.          

[v-huizhn-msft]

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

[v-huizhn-msft]

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

[ajay-sformula]

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.

[Ashish_Mathur]

Hi @ajay-sformula,

Refer to my solution here.

Hope this helps.

[v-huizhn-msft · Accepted solution]

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