[Power BI] Copy a column content from one table to another one (linked through common column)

[Fab117]

Hello,

I've 2 tables:

Table 1
Code	Quantity
A	5
B	3
A	4
C	6
B	4
A	2
C	5
A	3
B	1
C	4

Table 2
Code	Price
A	1.00 $/unit
B	1.20 $/unit
C	1.40 $/unit

To simplify future calculation, I'd like to add in column one the corresponding price in each line

=> 

Table 1
Code	Quantity	Price
A	5	1.00 $/unit
B	3	1.20 $/unit
A	4	1.00 $/unit
C	6	1.40 $/unit
B	4	1.20 $/unit
A	2	1.00 $/unit
C	5	1.40 $/unit
A	3	1.00 $/unit
B	1	1.20 $/unit
C	4	1.40 $/unit

Would someone know the formulae to use in Power Query?

Thanks

Fab

[AlB]

Hi @Fab117 

If you want it in DAX, you can create a calculate column in Table1. See it all at work in the attached file.

Price per unit = 
LOOKUPVALUE(Table2[Price], Table2[Code], Table1[Code])

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @Fab117 

This can be done easily in Power query with a merge. Place the following M code in a blank query to see the steps. See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJVitWJVnICsozBLJCYCZjlDGSZwWVN4LJGcFlTuJgxXJ0hXBaoIxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Quantity", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Code"}, Table2, {"Code"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Price"}, {"Price"})
in
    #"Expanded Table2"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Fab117]

Hi,

Thank you very much for looking at my issue and for your proposal.

I looked at the Merge option, this is for sure a good solution.

I'm still also assessing the possibility to do it in Power Desktop with the LookUpValue formulae.

Not sure which one is the most appropriate in my case.

Happy new year

Fab

[Fab117]

Waiting to see if it was possible to do this in Power Query, I found a possibility to do it in Power Desktop in Data mode:

Methology applied

Target: "NetReg_file"

Source: "Prices"

In Data Mode in Power Desktop (not Power Query)

Select "New column"

Then type formulae

Name new column= LOOKUPVALUE(Source Table[Field to copy], Source Table [Common field],Target Table[Common field])

For me:

SP = LOOKUPVALUE(Prices[Standard price (GBP)],Prices[GMM],NetReg_file[GMM])

As I've 2 different prices to considere, I did excatly the same with second one:

NP = lookupvalue(Prices[Net Price (GBP)],Prices[GMM],NetReg_file[GMM])

But faced an unexpected issue:

I'm not sure if I'm doing something wrong or if it's a bug.

Did someone see what is going wrong?

Tks

Fab