How to create an aggregated table from an existing table using M language

[mochabits]

Hi,

I have a table - MemberJoin in Power BI which looks like below:

And I want to create an aggregated table from MemberJoin using M language. Is it doable? If yes, how to do it in M?

The corresponding SQL statement is below, and it's followed by a screenshot of what the new table should look like.

select [YTD Flag], [Member Type], year([Join Date]) "Year", MONTH([Join Date]) "Month", datename(MONTH, [Join Date]) "MonthName",
[Join Channel],  COUNT(*) "Count", 
cast(count([YTD Flag]) as float) / cast(sum(count([YTD Flag])) over (partition by year([Join Date])) as float) "Prcnt by Year"
from [MemberJoin]
group by [YTD Flag], [Member Type], [Join Channel], year([Join Date]), MONTH([Join Date]), datename(MONTH, [Join Date]) 
order by [YTD Flag] desc, [Member Type] desc, Year desc, Month, [Join Channel]
;

[MFelix · Accepted solution]

Hi @mochabits,

On the advance query you need to do a group by and select all the fields you want to keep in your table and then select the one that you want to count.

Check this link and view the part about group rows.

Regards,

MFelix