Merge Question

[erhan_79]

Hi there ;

İ have two tables as below Table A and Table B , just i want to create lookup , and on table B i want  to see currency , but when i use merge sytem gives me 8 rows data as i attached "merged data" picture , i merged the data according to " Keycode" columns

TABLE A 

TABLE B

Merged Data :

as you see system creating 8 rows and duplicating somethings , i just would like to lookup value as below but on query side , how can i create  a lookup for currency as below on query side ? can you help me ?

[mwegener · Accepted solution]

Hi @erhan_79 ,

make a copy of Table A and group it by keycode and currency.

This should then be your lookup table.

[FrankAT]

Hi @erhan_79,

in Power Query you can do it like this:

// TableA
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzMNQzMjC0UNJRMjE1NjRIBDJA2NAURBgYAMnU0iKlWJ1oJSOcag0w1BqjqTVMAjJA2MgATIDI0uIUsFoTXGqN0dTGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #" Keycode Material Order Qty Value" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{" Keycode Material Order Qty Value", "Keycode"}, {"(blank)", "Material"}, {"(blank).1", "Order Qty"}, {"(blank).2", "Value"}, {"(blank).3", "Currency"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Order Qty", Int64.Type}, {"Value", Int64.Type}})
in
    #"Changed Type"


// TableB
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQMzDXMzIwMlDSUTIxNTY0SAQyQNjUVClWB6jACJcCM6gCYzQFhklABgibQxWY4lJgYaAUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date<u+202d> = _t, #"<u+202c>Keycode" = _t, Material = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date<u+202d>", type date}, {"Qty", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"<u+202c>Keycode"}, TableKeyCode, {"Keycode"}, "TableA (2)", JoinKind.LeftOuter),
    #"Expanded TableA (2)" = Table.ExpandTableColumn(#"Merged Queries", "TableA (2)", {"Currency"}, {"Currency"})
in
    #"Expanded TableA (2)"


// TableKeyCode
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzMNQzMjC0UNJRMjE1NjRIBDJA2NAURBgYAMnU0iKlWJ1oJSOcag0w1BqjqTVMAjJA2MgATIDI0uIUsFoTXGqN0dTGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #" Keycode Material Order Qty Value" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{" Keycode Material Order Qty Value", "Keycode"}, {"(blank)", "Material"}, {"(blank).1", "Order Qty"}, {"(blank).2", "Value"}, {"(blank).3", "Currency"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Order Qty", Int64.Type}, {"Value", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Keycode", "Currency"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Keycode"})
in
    #"Removed Duplicates"rrency"}}),
    #"Changed Ty

Regards FrankAT#"(blank)" = _t, #"(blank).1

[jdbuchanan71]

@erhan_79   Add a column to your table B like so.

Curr = LOOKUPVALUE ( 'Table A'[Currency], 'Table A'[Keycode], 'Table B'[Keycode] )

[erhan_79]

@jdbuchanan71 

thank you but , i want to create new colum on query side , i think you gave me DAX formula