Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.