Forum Discussion

HelpWanted's avatar
HelpWanted
Regular Visitor
3 years ago
Solved

Merging Tables Without Expanding Column

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?

 

Need Help
  • latimeria's avatar
    latimeria
    3 years ago

    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"

     

     

1 Reply

  • latimeria's avatar
    latimeria
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    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"