Forum Discussion

sramamoorthy23's avatar
sramamoorthy23
Frequent Visitor
3 years ago

Excel Power Pivot conditional bridging of two tables

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 Piv...
Need Help
BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

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's avatar
sramamoorthy23
Frequent Visitor
3 years ago

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's avatar
    BA_Pete
    Icon for Super User rankSuper User
    2 years ago

     

    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