[PowerQuery] add (same) column from another table

[Jamesmry]

Hi,

I just stumbled on a difficulty for which I cannot find a simple solution yet : I am trying to add another a column from one table/query to another.

It would look like this:

ColumnA - Original Table	ColumnA - Second Table
ValueA	ValueA
ValueB	ValueB
ValueC	ValueC
...	...

From what I can understand, there is no available functionnality available to perform such operation (this post - in french - explains it well).

I tried the following but I get stuck eventually:

• Adding a custom column and write the following script:

#"Second Table"[ColumnA]​

• Custom columnIt gives me the following result:
  Added columns
• Eventually I get stuck here as my options are pretty limited and none are convenient (there is a missing third option allowing values to be entered instead of messing around with the rest of table):
  Options missing(the options are in the same order in english)

Therefore I have tried something else (similar to what is suggested in the link at the beginning) :

1. Duplicate my original table and renaming it so the following steps do not interfere with the original data (unlike "referencing" it, which would).
2. Deleting every column except the one that is needed (the result is having only one column left).
3. Fusion queries/table using the available functionnality and perform a full outer join:
   full outer join

4.  Develop columns and "finally" get the excepted result.

Note: the content has been hidden to avoid sharing sensitive data.

So my question is the following: why is PowerQuery missing such useful funtionality embedded in its interface?

Additionally, why is the "Combine files" option grayed where my files are located in the same folder and share the same structure (which would have simplified my work and probably avoid this issue)?

[v-echaithra]

Hi @Jamesmry ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @Jamesmry ,

Thank you @MFelix , @pankajnamekar25  for your inputs.
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you.

[Jamesmry]

Hi @v-echaithra 

Thank you. I have replied to them, I also would appreciate your expertise for that question:

So my question is the following: why is PowerQuery missing such useful funtionality embedded in its interface?

Additionally, why is the "Combine files" option grayed where my files are located in the same folder and share the same structure (which would have simplified my work and probably avoid this issue)?

Kind regards,

[MFelix]

Hi @Jamesmry ,

Does the two tables have some common field like an ID or do you just want to copy the column exactly in the same position so for the first line you have the first value and so on?

If there is a common column then you need to do a merge based on that common column and that will give you a new column that you can then expand.

If however you don't have a common column then it's important to be aware of:

• Number of rows match between both tables
• You must sort both tables properly before doing the next steps

For you to have this working you need to do the following steps:

• Sort the tables in the proper order (if needed)
• Add a Index column to your tables (on both of them)
• Now do the merge based on that index column on the table you want and then expand the column from the other table

Has I mentioned be carefull with the number of rows because you can have more values or less than you need.

[Jamesmry]

Hi @MFelix,

Thank you for your asnwer. To answer your question :

• the tables have more than one common column (as shown in the screenshots, althrough written in French).
• I want to be able to merge the tables so that the values of the common column are exactly at the same position (as shown in the table in my post).

---

@MFelix wrote:

If there is a common column then you need to do a merge based on that common column and that will give you a new column that you can then expand.

---

That is exactly what I am trying to do and as explained in my post.

Providing those answers, what is your opinion now on the subject?

I have not thought about the index column. I’ll give that a go as well.

[pankajnamekar25]

Hello @Jamesmry 

Try this code

let
Source = Original,
Merged = Table.NestedJoin(
Source,
{"ColumnA"},
Second,
{"ColumnA"},
"SecondTable",
JoinKind.LeftOuter
),
Expanded = Table.ExpandTableColumn(
Merged,
"SecondTable",
{"ColumnA"},
{"ColumnA_Second"}
)
in
Expanded

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[Jamesmry]

Hi @pankajnamekar25,

Thank you for your answer.

I would like to avoid using the M script but let's say I try & use it: how do you implement your script when I already have a Table.NestedJoin(...) function embedded in one of the tables I would like to merge?

Let me explain: one or both tables are already the product of merged data.

Therefore, in one of them (which I would like to use to merge with a new one), I already have the following embedded M script:

let
    Source = Table.NestedJoin(#"Table1", {"ColumnA"}, #"Table2", {"ColumnA"}, "Table1", JoinKind.FullOuter)
in
    Source

How do you implement yours in this?

[v-echaithra]

Since your query already includes a Table.NestedJoin, there’s no need to change it, simply add another merge step that builds on the existing result.

let
Source = Table.NestedJoin(
#"Table1",
{"ColumnA"},
#"Table2",
{"ColumnA"},
"Table2_Data",
JoinKind.LeftOuter
),

#"Expanded Table2" = Table.ExpandTableColumn(
Source,
"Table2_Data",
{"ColumnFromTable2"}
),

#"Merged Table3" = Table.NestedJoin(
#"Expanded Table2",
{"ColumnA"},
#"Table3",
{"ColumnA"},
"Table3_Data",
JoinKind.LeftOuter
),

#"Expanded Table3" = Table.ExpandTableColumn(
#"Merged Table3",
"Table3_Data",
{"ColumnFromTable3"}
)
in
#"Expanded Table3"

Handle each merge as a separate step in your query. Every merge creates a new intermediate table, and you should continue building on that result. After each merge, expand the nested column right away to keep the structure flat and easy to manage.

Avoid working inside nested columns like "Table1". These are not regular columns and shouldn’t be used for further merges. Instead, always perform joins at the main table level.

In short, keep your existing Table.NestedJoin as is, simply add another merge step on top of its output and expand it immediately to maintain a clean and maintainable query.

Hope this helps.

[Jamesmry]

Hi @v-echaithra,

Thank you for your detailed answer.

Would you mind answering the other one? About the missing function & grayed option?

My workflow has morphed a bit and have stand-byed the task requiring the answers to this post. Therefore I will wait until I have the opportunity to test & accept one of the suggested solutions.