Excel Power Pivot conditional bridging of two tables

[sramamoorthy23]

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 QueryFinal Student Availability Matrix 

[sramamoorthy23]

Any Clue?

[BA_Pete]

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

[sramamoorthy23]

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.

[BA_Pete]

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