cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_Taylor
Advocate II
Advocate II

Power Query M - Merge count of rows into another table

As I am sure most issues with Power Query are, I know how to do it in Excel - but have no idea how to accomplish it in Power Query M. 

 

Just to be clear, I am using the Dataflows page from Dataverse (on the Power Apps page) to manipulate a Dataverse table. Using two tables, I am trying to create one new table.

 

The two tables are:

1. Students - a reference table with unique rows for each student.

2. Behaviour Issues - non-unique rows that list each time a student breaks the rules. I would liken it to monthly transactions at a register: some people come in more than once in a month and I am trying to count various common categories.

 

Students table:

J_Taylor_0-1665629019118.png

 

Behaviour Issues:

J_Taylor_1-1665629134577.png

 

I am aware of "Group By". However, this displays the data in its own table.

> Count of total of each student

J_Taylor_2-1665629207790.png

>Count of each student and issue1

J_Taylor_3-1665629239517.png

>Count of each student, issue1 and issue2 (I could have just done student and issue2, since issue2 is unique to issue1)

J_Taylor_4-1665629309150.png

 

I have created a sheet showing the desired outcome (attached and below).

J_Taylor_5-1665629953795.png

 

Not that it is much, but in Excel, this would be easy. For Total, I would just use CountIf looking for each time the student number occurred. The other columns would be CountIFS looking for the student number and each issue (referencing the headers). 

 

I don't know if "Group By" is part of the answer. I looked at "Merge Queries", but didn't get far enought with grouping to try. Hopefully, my explanation is clear. Any help to achieve my final table would be much appreciated.

1 REPLY 1
J_Taylor
Advocate II
Advocate II

I never found an answer for this. Instead, I linked the tables directly with PowerBI and manipulated them through there. I used DAX measures, Power Query (merge) and custom columns to get what I needed.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (5,759)