Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a student Brekky Roster (sqlserver view) with dates of roster and a Student availability sqlserver table with Absence status. I need to create an Student availability matrix for a month in Pivot table. The screenshots of table and the final view to be displayed in pivot is as below:
Problem: Student id in the both the tables are matching however some of the dates are matching. I have tried to create relationship between tables through a calendar table in power pivot and a bridging table as these two tables have many to many relationship. Created a measure to diaplay the availability status in values field of pivot using Concatenatex function. However, I am not able to drag the measure to final pivot values field. Can you please help on this?
Excel tables created through Power Query
Final Student Availability Matrix
Any Clue?
Hi @sramamoorthy23 ,
Create a new column on each of your original tables in Power Query called [studentDate]:
Text.Combine({[StudentId], [Date]}, "-")
You should now be able to merge the two tables together on TableA[studentDate] = TableB[studentDate].
Pete
Proud to be a Datanaut!
Thanks @BA_Pete For your quick response.
Please note that columns in the both tables are different. I need to display the Student availability irrespective of the job selected in the report filter. Based on the student id the availability from Student absense table must be filled in the value area of pivot table. If I merge table as Left outer join then I will get null values for job and Leadership role in StudentBrekyRoster table. However, When I select the job number from the report filter in pivot I won't get the desired output. I hope its clear. If not please let me know I will send you the screenshots. I am stuck at this solution.
Sorry, I'm struggling to understand what you're going for here.
Implementing my solution will give you a dataset capable of providing the output that showed in your original post.
You may need to adjust the front-end to display exactly how you want it to, but the Power Query solution has been provided.
Pete
Proud to be a Datanaut!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |