Adding Column from another query with value type per row

Question

Hi&nbsp;I have these two querys resulting in these tables&nbsp;TableAIDName1john2dave3jane4steve&nbsp;TableBIDValueNameValue1age201gendermale1marriedyes2age222gendermale2marriedno3age303genderfemale3marriedyes4age334gendermale4marriednoI want to add some of the ValueName values from TableB as columns in TableA to get this&nbsp;IDNameagegender1john20male2dave22male3jane30female4steve33male&nbsp;I'm still very much on the learning curve here and any help will be very much appriciated.&nbsp;Thank you&nbsp;&nbsp;&nbsp;

m_dekorte · Accepted Answer

Hi&nbsp;John255,&nbsp;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.&nbsp;To illustratelet
    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&nbsp;with this result.&nbsp;Ps. If this helps you solve your query, please mark it as solution. Thanks!

Forum Discussion

Adding Column from another query with value type per row

2 Replies

Related content

Adding columns

Adding a column

Sort by another column

Renaming columns and adding columns

Find Most Recent Date Relative to another Date Column in Power Query

ID	ValueName	Value
1	age	20
1	gender	male
1	married	yes
2	age	22
2	gender	male
2	married	no
3	age	30
3	gender	female
3	married	yes
4	age	33
4	gender	male
4	married	no

ID	Name	age	gender
1	john	20	male
2	dave	22	male
3	jane	30	female
4	steve	33	male