Add new column based to dataset based on another table

[Anonymous]

Hi All, 

I wish to add a new column, "Type", based on another table, how would i do that in the query? 

Thanks alot for your help. much appreciated!!!!

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create two blank queries, and put the following codes in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8otSitWJVnIMBVOuQCoWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1)
in
    #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFKL0pNzQPShkqxOtFKBamJRUBOUk5icjaQNgILJmekFhVVgoVLQVpMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, colour = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"colour", type text}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Index"}, Query1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Type"}, {"Type"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Index"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruits", type text}, {"Colour", type text}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Type"}, {"Type"})
in
    #"Expanded Custom"

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create two blank queries, and put the following codes in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8otSitWJVnIMBVOuQCoWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1)
in
    #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFKL0pNzQPShkqxOtFKBamJRUBOUk5icjaQNgILJmekFhVVgoVLQVpMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, colour = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"colour", type text}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Index"}, Query1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Type"}, {"Type"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Index"})
in
    #"Removed Columns"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.