Copy data from another table

[Royoam]

Hi, I m new to PowerBi. Currently, I encountered an issue about getting data from one table to another table, below is my trouble:

Table 1
Case number	Description	Response	Resolution
1234	zzzzzzz	xxx	yyy
Table 2
Number	Target	Wanted value
1234	Response	xxx
1234	Resolution	yyy

Table 1 initially gets two column fields only, I want to add two columns "response" & "resolution" into it in which their value should be referred from table 2. Table 1 [Case number] & Table 2 [Number] formed the relationship.

Please help to advise how to proceed. Thanks !!

[Syndicate_Admin]

Hi @Royoam 

Merge table1 and table2, then get the column value

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUaqCAKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case number" = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case number", Int64.Type}, {"Description", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Case number"}, Table2, {"Number"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Response", each Table.SelectRows([Table2], each [Target]="Response")[Wanted value]{0}?),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Resolution", each Table.SelectRows([Table2], each [Target]="Resolution")[Wanted value]{0}?),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Table2"})
in
    #"Removed Columns"

Or you can pivot table2 first, then merge two tables, no need to add columns, this is table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQpKLS7IzytOBTIrKiqUYnWQZfJzSksy8/OAnMrKSqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Target = _t, #"Wanted value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Target", type text}, {"Wanted value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Target]), "Target", "Wanted value")
in
    #"Pivoted Column"

[Anonymous · Accepted solution]

Hi @Royoam 

Merge table1 and table2, then get the column value

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUaqCAKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case number" = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case number", Int64.Type}, {"Description", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Case number"}, Table2, {"Number"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Response", each Table.SelectRows([Table2], each [Target]="Response")[Wanted value]{0}?),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Resolution", each Table.SelectRows([Table2], each [Target]="Resolution")[Wanted value]{0}?),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Table2"})
in
    #"Removed Columns"

Or you can pivot table2 first, then merge two tables, no need to add columns, this is table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQpKLS7IzytOBTIrKiqUYnWQZfJzSksy8/OAnMrKSqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Target = _t, #"Wanted value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Target", type text}, {"Wanted value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Target]), "Target", "Wanted value")
in
    #"Pivoted Column"

[Royoam]

Thanks a lot...it works..