M Code Load Issue with a Duplicated Query

[mramstead1]

Hello all,

I have a 30,000 row query that tracks the activities completed by our service team. One of the things we are trying to track is how many activities are completed for individual clients. The problem is we have multiple names for some clients that share the same client code which messed up aggregation of data on the visual side.

In order to clean it up, I duplicated the 30,000 row query, only kept client code and client name columns, removed duplicate client codes (to create a mapping table) and then disabled this query from loading into the model. This mapping table causes little to no strain on the model loading time.

This is the code for the main data table before I added the name transformation:

This data (along with my date table and two small mapping tables) takes about 1 minute to load into the visualizations (50 mb). 

Here is the added code in order to get all of the client names to be the same (Same code as above with additional steps):

This stops the model from loading completely (Goes up into the gigabytes of loaded data taking hours. I am guessing this has to do with the table referencing but I am not sure why it is breaking. I am still learning Power BI/Query and by no means have best practices down.

Here's my query dependencies (in case that is important):

If there is a better bit of code to use in order to get the same result or if I need to do something altogether, I would love some feedback!

Thank you in advance for the help!

[watkinnc · Accepted solution]

If what you are trying to do is have a table of unique LookupCodes that relates to the fact table LookUp codes, and want to display a Client name from the Dimension table, couldn't you make a new query with just the two columns, like:

= Table.Distinct(FactTable[[LookupCode], [Client]], "LookupCode")

And then just set the relationship with LookupCode on the one side, and the LookupCode column in the fact table as the many side?

--Nate

[mramstead1]

Thanks for replying Nate! That is an option that should work, I was just concerned on the memory issues that might occur loading another query into the model. The client list is under 5,000 at the moment, but that could grow significantly throughout the year (the data starts at 1/1/22 and compounds throughout the year).

I have a similar bit of M Code in another query that gets appended into the main Activity query, so I thought I would be able to achieve something similar with client names. Here's a picture of the code from the query with the working code:

If this is an inefficient way to model I will more than happily use the code you listed above, I'm just trying to understand why the CSR24 query code works and the other code loads eternally, most likely due to a circular reference that I overlooked.

Either way I look forward to hearing your thoughts on this.