Merging queries with a many to many relationship

[HB13]

Hello PBI People!

I am struggling with a merge task and I'm hoping for some clarity and assistance on the issue.

I have two tables, Policies and Risks
Policies records the policyId every single day that a policy remains active
Risks contains info about different risk vehicles linked to a policy (one policy can have many vehicles)

I have included a screenshot of an example of the two tables as well as the merged table I am trying to create. I basically want to map each riskvehicleid to every instance of the policyid in the policies table.
I am not too sure how to go about it Power BI, my last attempt only pulled one row into the merged table. 

Please advise!
Thank you in advance

[v-jianboli-msft]

Hi @HB13 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

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.

[Bubble4502 · Accepted solution]

Hi @HB13 ,

You can achieve this goal in power query:

First, choos merge queries as new:

Then select table and choose inner join:

Expand risk and remove useless column:

Final output:

Here is the M code:

let
    Source = Table.NestedJoin(Policies, {"policyid"}, Risk, {"policyid"}, "Risk", JoinKind.Inner),
    #"Expanded Risk" = Table.ExpandTableColumn(Source, "Risk", {"policyid", "riskvehicleid", "vehiclemanufactureyear"}, {"Risk.policyid", "Risk.riskvehicleid", "Risk.vehiclemanufactureyear"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Risk",{"Risk.policyid"})
in
    #"Removed Columns"

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@HB13 , Based on what I got. Create a combine column or policy and date in both tables and use that in merge

[HB13]

Are you saying combine date and policyid in a column? There isn't a date column in the Risks table to do so