Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
6 years ago
Solved

Merge Question

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 ?

 

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi erhan_79 ,

     

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

    This should then be your lookup table.

     

4 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi erhan_79 ,

     

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

    This should then be your lookup table.

     

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    erhan_79   Add a column to your table B like so.

    Curr = LOOKUPVALUE ( 'Table A'[Currency], 'Table A'[Keycode], 'Table B'[Keycode] )
    • erhan_79's avatar
      erhan_79
      Post Prodigy
      6 years ago

      jdbuchanan71 

       

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

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    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