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 "...
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.

     

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