Forum Discussion
Excel combination of tables
Hello,
I'm trying to figure out the best way to get a table with information from two seperate tables. In the first table I got article numbers in column A. Each article consists of multiple components as listed in column D. In the second table I got a list of some components which are listed in column D in the first table that also consist of multiple 'sub'components, listed in column H. What I'm trying to reach is to get an table with in column A the article numbers and in column B all the components but also the subcomponents.
So in case of the example below I would like to have a table like this. What's the best way to do this? Thanks in advance?
| Code | Component |
| 11023 | VP523553 |
| 11023 | VP100451 |
| 11023 | BV001 |
| 11023 | GR456228 |
| 11023 | GR386382 |
| 11023 | GR360061 |
| 11023 | GR100004 |
| 11023 | GR405241 |
You may use Power Query to merge tables
like
let Source = Table.NestedJoin( Table1, {"Onderdeel Code"}, Table2, {"Deeg"}, "Table2", JoinKind.LeftOuter), ExpandTable2 = Table.ExpandTableColumn( Source, "Table2", {"Code"}, {"Code.1"}), AddComponent = Table.AddColumn( ExpandTable2, "Component", each if [Code.1] = null then [Onderdeel Code] else [Code.1] ), SelectColumns = Table.SelectColumns( AddComponent, {"Code", "Component"}) in SelectColumnsPlease check attached.
You may use Power Query to merge tables
like
let Source = Table.NestedJoin( Table1, {"Onderdeel Code"}, Table2, {"Deeg"}, "Table2", JoinKind.LeftOuter), ExpandTable2 = Table.ExpandTableColumn( Source, "Table2", {"Code"}, {"Code.1"}), AddComponent = Table.AddColumn( ExpandTable2, "Component", each if [Code.1] = null then [Onderdeel Code] else [Code.1] ), SelectColumns = Table.SelectColumns( AddComponent, {"Code", "Component"}) in SelectColumnsPlease check attached.
Thanks a lot, used the power query in my own excel and it works fantastic. You made my day.
You are welcome
