Adding Column from another query with value type per row

[John255]

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	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

I want to add some of the ValueName values from TableB as columns in TableA to get this

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

I'm still very much on the learning curve here and any help will be very much appriciated.

Thank you

[John255]

Hi @m_dekorte 

Yes, thank you does what I need 🙂

[m_dekorte · Accepted solution]

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!