Forum Discussion
Problem with a relationship many to many
Have 2 tables, the relationship between the is one to many.
Table1 is like this:
Level | facctor |
1 | 1.06 |
2 | 0.13 |
3 | 40.92 |
2 | 1.06 |
Table2 is:
Level | Cost |
1 | 30.96 |
1 | 12.25 |
2 | 13.25 |
1 | 50 |
3 | 48.02 |
The result I want is:
level | factor | cost | final cost |
1 | 1.06 | 30.96 | factor * cost |
1 | 1.06 | 12.25 | factor * cost |
2 | 0.13 | 13.25 | factor * cost |
1 | 1.06 | 50 | factor * cost |
3 | 40.92 | 48.02 | factor * cost |
2 | 1.06 | 13.25 | factor * cost |
Becoming crazy!!!
Hi lbarretta ,
Please try:
First, duplicate table1, then add a new column cost:
Expanded the cost column:
Then add a new column final cost:
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUMzBTitWJVjICcgz0DI3BHGMgx8RAz9IILgVRFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, factor = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"factor", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "cost", each Table.SelectColumns( Table.SelectRows(Table2, (x)=>x[Level]=[Level]), "Cost")), #"Expanded cost" = Table.ExpandTableColumn(#"Added Custom", "cost", {"Cost"}, {"cost.Cost"}), #"Added Custom1" = Table.AddColumn(#"Expanded cost", "final cost", each [factor]*[cost.Cost]) in #"Added Custom1"Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- MAwwad
Solution Sage
Hello,
Its not possible to have a relationship between two tables where the primary key between these two tables is duplicated.
You will have a duplication error. or Multiple tables where only 1 table expected message.
What you can do to make the Primary Key between the two tables as unique is to try to Concatenate the Primary key with any other key or identifier, this purpose is to make the PK unique or no valid relationship between the two tables can happen
Hi!
thanks for your prompt answer.
at lest I don't need to modify the structure of the relationship and what i need is that for every record in the second table with level 2 to show 2 records with a different computed final cost...
- Mikelytics
Resident Rockstar
Hi lbarretta
I recommend you to do it in Power Query like this
Table 1
Table2
Execute a merge
Expand the cost column from table 2
add the final cost column
Result
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi Mike, sorry if i didn't answer before. what you did look interesting but i didn't realize what the first 2 steps do.
v-jianboli-msftCommunity Support
Hi lbarretta ,
Please try:
First, duplicate table1, then add a new column cost:
Expanded the cost column:
Then add a new column final cost:
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUMzBTitWJVjICcgz0DI3BHGMgx8RAz9IILgVRFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Level = _t, factor = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"factor", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "cost", each Table.SelectColumns( Table.SelectRows(Table2, (x)=>x[Level]=[Level]), "Cost")), #"Expanded cost" = Table.ExpandTableColumn(#"Added Custom", "cost", {"Cost"}, {"cost.Cost"}), #"Added Custom1" = Table.AddColumn(#"Expanded cost", "final cost", each [factor]*[cost.Cost]) in #"Added Custom1"Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
