Group All Rows - Possible to keep table Relationships?

[SamuelAB]

I have a fact table that has multiple elements associated with it, these elements have their own relationship tables.

Table 1 - Elements in model

Key	Name	Type
1	Table	4x6'
2	Chair	Standard
3	Chair	Large

Table 2 - Properties of these elements

Key	ParameterDescriptor	ElementKey	Value	Text Value
1	1	1	4	4 feet
2	2	1	6	6 feet
3	3	2	3	3 feet
4	4	2	2	2 feet

Table 3 - Parameter Descriptor

Key	Name	Units
1	Lenght	feet
2	Width	feet
3	Seat Height	feet
4	Width	feet

The elements and the parameters are considered facts, so I am looknig to merge them.

To optimize the database, I learned about Grouping and the special "All Tabs" option in grouping.

https://learn.microsoft.com/en-us/power-query/group-by

https://www.thepoweruser.com/2019/07/30/grouping-rows-with-power-bi-power-query/

This seems like irt could be a solution. So I take all the paramters and merge with the elements. Then I group by Element IDs and all my parameters get stored in a special table I can seemingly only access with DAX (this part is realy not well documented).

What I did notice is thast the embedded/grouped parameter mini table seems to lose its relationship with Table 3, the Parameter Descriptor. Is there a way to keep this connection active? Based on my description, is grouping the best way to manage this element parameter data?

We want to be albe to easily count elements, but also pull out all their parameter and specific parameter when the time comes.

[v-yanjiang-msft]

Hi @SamuelAB ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @SamuelAB ,

Not very clear about your expected result, but why not just use Home>Merge Queries instead of Group By.

Then both select the Key column as matching column. As there're more keys in the second table, you can choose Right Outer join kind here.

After merge, they are in the same table.

If you don't want to change the original table, you can choose Merge Queries as New.

Best Regards,
Community Support Team _ kalyj

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