Forum Discussion

John255's avatar
John255
New Member
3 years ago
Solved

Adding Column from another query with value type per row

Hi   I have these two querys resulting in these tables   TableA ID Name 1 john 2 dave 3 jane 4 steve   TableB ID ValueName Value 1 age 20 1 gender ma...
Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    Hi John255,

     

    You can use the Merge Query option on the Ribbon, setting the ID as key to both tables, this will return a table. When we transform that table into a record, we can expand a selection of its fields to new columns.

     

    To illustrate

    let
    Source = Table.NestedJoin(TableA, {"ID"}, TableB, {"ID"}, "TableB", JoinKind.LeftOuter),
    ReplaceValue = Table.TransformColumns( Source, {{ "TableB", each Record.FromList( _[Value], _[ValueName] ) }} ),
    ExpandRecordFields = Table.ExpandRecordColumn(ReplaceValue, "TableB", {"age", "gender"}, {"age", "gender"})
in
    ExpandRecordFields

     

    with this result.

     

    Ps. If this helps you solve your query, please mark it as solution. Thanks!

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

Hi John255,

 

You can use the Merge Query option on the Ribbon, setting the ID as key to both tables, this will return a table. When we transform that table into a record, we can expand a selection of its fields to new columns.

 

To illustrate

let
    Source = Table.NestedJoin(TableA, {"ID"}, TableB, {"ID"}, "TableB", JoinKind.LeftOuter),
    ReplaceValue = Table.TransformColumns( Source, {{ "TableB", each Record.FromList( _[Value], _[ValueName] ) }} ),
    ExpandRecordFields = Table.ExpandRecordColumn(ReplaceValue, "TableB", {"age", "gender"}, {"age", "gender"})
in
    ExpandRecordFields

 

with this result.

 

Ps. If this helps you solve your query, please mark it as solution. Thanks!