Forum Discussion

ajay-sformula's avatar
ajay-sformula
Regular Visitor
8 years ago
Solved

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                                ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

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