Merging Tables Without Expanding Column

[HelpWanted]

I am attempting to merge two tables (with the left table already having been merged previously creating a table column). I am wanting to merge these tables without first expanding the column that is being used in that merge.

I have attempted to use Table02[Column2] as the column name, but I guess it doesn't like that.

To which turns into the error: 

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=[List]
    Type=[Type]

So my questions are:

• Is it possible to merge with Table02[Column2] = "Column2", or am I just going to have to expand Table02 first?
• If so, how would this be done?

[latimeria · Accepted solution]

Hi @HelpWanted ,

If I understood well what you are trying to do :

table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyjZU0lEqMUo2KjJUitUB8o1gfCOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])
in
    Source

table3:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyjZU0lEqMU42KjJUitUB8o1gfCMI3xjKLzFWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])
in
    Source

 merge table with t1 & t2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyjZU0lEqMUw2KjJUitUB8o1gfCOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Column1"}, T2, {"Column1"}, "T2", JoinKind.LeftOuter),
    Custom1 = Table.TransformColumns(
        #"Merged Queries",
        {
            {"T2", 
            each Table.NestedJoin(_, {"Column1"}, T3, {"Column1"}, "T3", JoinKind.LeftOuter)
            }
        }
    ),
    #"Expanded T2" = Table.ExpandTableColumn(Custom1, "T2", {"Column2", "T3"}, {"T2.Column2", "T2.T3"}),
    #"Expanded T2.T3" = Table.ExpandTableColumn(#"Expanded T2", "T2.T3", {"Column2"}, {"T2.T3.Column2"})
in
    #"Expanded T2.T3"