Forum Discussion
Helper IV[Power BI] Copy a column content from one table to another one (linked through common column)
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
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.
4 Replies
- AlB
Community Champion
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.
Fab117Hi,
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
- 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.