Merging Fact Table and a dimension Table

[BIlix]

Hello Community,

i have a problem regarding a Merge in PQ.

I have a Dimension Table Called DeskDistrib

Columns DeskdistriID, ProjectID, ...

And a Fact Table

FactProject
Columns FactProjectID, ProjectID, StoryPoints...

I want to merge both tables, they have a one to many relationship from Dimension to facttable via ProjectID

After i merged the table, there are way too many entries for the storypoints.

I can only merge via ProjectID, because it is the only column in common for both tables.

Any idea how i should perform the merge or maybe group the data, so i can avoid the multiple entries for the storypoint?

[v-yanjiang-msft · Accepted solution]

Hi @BIlix ,

Based on your description, if the relationship between dimension table and fact table is one to many, then after merge, the result should be adding a DeskdistriID column in the fact table based on the same ProjectID.

The problem will occure when it's a many-to-many relationship between your Dimension Table and Fact Table. To avoid this, you can try grouping the data in your Fact Table by the ProjectID column before merging with the Dimension Table.

To do this in Power Query, you can select the Fact Table and then use the "Group By" feature under the "Transform" tab. In the "Group By" window, select the "ProjectID" column and choose an aggregation method for the "StoryPoints" column, such as "Sum" or "Average". This will group the Fact Table by ProjectID and aggregate the StoryPoints values.

Once you have grouped the data in the Fact Table, you can then merge it with the Dimension Table using the ProjectID column as the join key. This should result in a single entry for each ProjectID in the resulting merged table.

Best regards,

Community Support Team_yanjiang

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