how to create new table in power bi depended on Max date from another table

[Lily_DS]

In powerbi I have table42 with 3 columns ( productId , amount , date) i need to creat a new table depends on this table(table42) with filter return only max date for each product and amount so i can calaculate the total amount

below my condation in sql

case when d.Date=(SELECT max(z.Date) FROM table42 zz WHERE d.prdAcctId=zz.prdAcctId)

I tried this but I don't get any result

= #table({"prdAcctId","date", "amount"},{table42[prdAcctId],table42[date],DATEVALUE(MAX('Table42'[DATE]))}

thanks in advance.

[Greg_Deckler · Accepted solution]

@Lily_DS Seems like you are mixing Power Query and DAX together. Here is a DAX solution. You would create a new table in the Desktop (not Power Query Editor)

Table = SUMMARIZE('Table42',[prdAcctId],"Date",MAX('Table42'[DATE]))

[v-chenwuz-msft · Accepted solution]

Hi @Lily_DS ,

I think you want "Group By" function in power query, please refer this link.

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

Best Regards

Community Support Team _ chenwu zhu

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

[Greg_Deckler · Accepted solution]

@Lily_DS Seems like you are mixing Power Query and DAX together. Here is a DAX solution. You would create a new table in the Desktop (not Power Query Editor)

Table = SUMMARIZE('Table42',[prdAcctId],"Date",MAX('Table42'[DATE]))

[Lily_DS]

thank you your comment really help me i catch💡 why always getting errors! i'm a beginner, do you have any resource clarify the Power Query and DAX ?