Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Get values on one table from another master table

I have two tables in PowerBI connected to 2 different sources. I want to be able to update the 'sales' column values in Table1 by getting the values from 'sales column' in MasterTable. Please help me how to get this done!

 

Need Help
Tutorial Requests

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous 

     

    How the query editor make a merge between both table by the product then expand the sales and delete the previous column.

     

    Check Code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgM9U6VYnWglJyAbzDWC8J1hfAjXBcg2BvPNgQKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Sales = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Sales", Int64.Type}, {"Price", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product"}, MasterTable, {"Product"}, "MasterTable", JoinKind.LeftOuter),
    #"Expanded MasterTable" = Table.ExpandTableColumn(#"Merged Queries", "MasterTable", {"Sales"}, {"MasterTable.Sales"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded MasterTable",{"Sales"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"MasterTable.Sales", "Sales"}})
in
    #"Renamed Columns"