Problem with a relationship many to many

[lbarretta]

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!!!

[v-jianboli-msft · Accepted solution]

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.

[Mikelytics]

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.

-----------------------------------------------------

LinkedIn

[lbarretta]

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.

[MAwwad]

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

[lbarretta]

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...