Solved: How to create query

MissLilly · ‎05-28-2024

I am new to PowerBi and am struggling to create a simple query. I've created a two new columns as I want one to show a count of the 2023 and 2024 year from the column below, and the other column to show the a count of the remaining years in the column. But I just can't get the queries right, can anyone help please?

BA_Pete · ‎05-29-2024

Hi @MissLilly ,

To do this in Power Query, you can select your [Next Review Date] column, go to the Home tab > Group By (or right-click on the column header and find 'Group By...'. The aggregation column should default to the Count operator which is fine. Hit OK and you'll have your years with a count of rows next to them.

However, depending on your exact scenario, this probably isn't something that should be done in Power Query. Calculating counts/averages/sums etc should really be done with DAX in Power BI (after applying your Power Query to the data model).

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

View solution in original post

BA_Pete · ‎05-29-2024

Hi @MissLilly ,

To do this in Power Query, you can select your [Next Review Date] column, go to the Home tab > Group By (or right-click on the column header and find 'Group By...'. The aggregation column should default to the Count operator which is fine. Hit OK and you'll have your years with a count of rows next to them.

However, depending on your exact scenario, this probably isn't something that should be done in Power Query. Calculating counts/averages/sums etc should really be done with DAX in Power BI (after applying your Power Query to the data model).

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!