Which is the faster refreshing time for reference other column in M Power Query

[tanat_inc]

Hi community,

My Data model has two table :
1. f_visit_info Table to store visit and customer's action.
2. f_customer_info

I want to create a custom column show the last f_visit_info[visit_date]  of each f_customer_info[customer_id].

Option 1 : Create intermediate talbe which turned off "Enable load"
--> goup by [customer_id] , agregate visit_date with List.LAst() 
--> Merge f_customer_info with this intermediate table

Option 2 : Add custom column in f_cutomer_info
= Table.Group(f_visit_info,{"customer_id"},{"last_visit", each List.Last([visit_date])}) [last_visit]{0}

Issue is f_visit_info has somany query after loading from data source. I wonder if it will be repeat again in the process in Option 2, which will add another significant time to refreshing for repeating f_visit_info ETL query.

PS : I don't know why but when I added DAX custom column, it will make every "edit / add measure" action take very long time --> So I prefers to finish it before loading into the model with M power query;

[mahoneypat · Accepted solution]

This kind of thing should be done on the DAX side.  If you provide example data, a specific solution can be provided.  However, it is possible in M also.  You can merge the visit table into the customer table on the customer_id column. Don't expand the merged table, but add a custom column with = List.Max([MergedColumn][visit_date]).  That should give the max date from the visit_date column for that customer.  You can then remove the MergedColumn.

Pat

[amitchandak]

@tanat_inc , You can create a measure

max( last f_visit_info[visit_date] ) and use that in visual with f_customer_info[customer_id] or name 

or a new column in f_customer_info

maxx(filter( f_visit_info , f_visit_info[customer_id] = f_customer_info[customer_id] ),f_visit_info[visit_date])

[tanat_inc]

Thanks for your answer. Could you instructed me how to do this in power query instead ?

- I don't know why, but when I add DAX custom column to my model it wull cause significantly longer loading time when I create any new measure after that. 

>> That's why I try to avoid DAX ustom column, and want to do it in M Power query.

- Objective is to list the name of customer, the last time he visit, and how long they missing in table (matrix visual) along with their contact info. 

>> As I also need to display it on matrix visual, I need to create new column.