Correct way to link tables - relationships / index lookup?

[pmsilvbi]

Hi all

I'm struggling to find the best/most efficient way to link some of my tables together. I've tried creating relationships in PowerBi. The only option it will accept is many to many and whilst the data model loads fast,  the data returned in the visuals is not correct.

I've tried adding custom columns in Power Query using List.PositionOf to create an index then returning the results. This works great in Power Query! But when I close and apply the changes my model now takes 10+ minutes to load.  My main data source is only 19MB

Am I using the wrong methods? Is there a better way?

Any suggestions greatly appreciated.

[BA_Pete · Accepted solution]

Hi @pmsilvbi ,

Difficult to say with any certainty without seeing your model/data/understanding your tables/scenario, but if you're creating a star schema model, your dimension tables must have a unique field in them.

Re: the load time/size, I'm guessing it's because Power Query is scanning your table over and over again to produce the List.PositionOf results. You may be able to avoid this issue by using a buffer function but, again, without seeing your data, it's difficult to say. Either way, I personally don't think you want to be using List.PositionOf for the purpose that you are, it's highly likely there's a more 'correct' way to achieve your desired outcome.

If you can share some anonymised examples of the tables you're trying to relate, that would help me to visualise possible solutions.

Pete