cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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!

 

powerbicapture1.PNG

1 REPLY 1
Super User III
Super User III

Hi @grishbuzz 

 

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors