SharePoint list and unpivoted Power BI using Dates and Status columns of list.

[ljkeefe]

I have a SharePoint list that looks like this:

This is to track meetings we have to attend to get approval for projects. There are deadlines for those meetings so I've incorporated the meeting items and deadlines in the same list using the Status column to differentiate between an approved item and deadline. Any line with a deadline status has the meeting deadline in the Comment column and the date of the meeting for the deadline in the appropriate Meeting date column. 

The Deadline view looks like this: 

I've created a BI report that shows all meetings in a sequential list (unpivoted) for leadership, which looks like this:

Is there a way that I can take the Deadline Status item's Comment section show as a column on this leadership report by matching the Meeting Date and Meeting Type?

[V-yubandi-msft]

Thank you for staying engaged and sharing your concerns.

I understand that this issue might require a more detailed, hands on approach. While we've covered several possible solutions, I recommend reaching out to a certified Power BI partner for support tailored to your specific needs.

Power BI Partners – https://www.microsoft.com/en-us/power-platform/products/power-bi/partners#

Thanks& regards,

Yugandhar.

[V-yubandi-msft]

Hi @ljkeefe ,

Did you get a chance to contact the Power BI Partner using the link I shared earlier? If so, have you received any response from them yet? If you do hear back, it would be great if you could share the outcome, as it could really help others in the community who are facing similar challenges.

Thanks&regards,

Yugamdhar.

[ljkeefe]

@V-yubandi-msft No, I did not. I do not have it in my budget for that especially for something this minor. This report would be in addition to other tools. 

[V-yubandi-msft]

Hi @ljkeefe ,

I wanted to follow up to see if your issue was resolved after raising a support case. If a solution was found, it would be very helpful if you could share your insights with the community, as this could assist others facing similar challenges.

Thank You.

[V-yubandi-msft]

Hi @ljkeefe ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ljkeefe]

@V-yubandi-msft I am having the same issues and I can't figure it out. It only happens to some rows, it will bring in the deadline comments but it's the wrong one. I'd say for about 90% for the report the measures work but not for the other 10%.

[V-yubandi-msft]

Hi @ljkeefe ,

Thank you for your continued patience and collaboration throughout this thread. 

I have taken the sample data and reproduced your scenario. Attached is the sample pbix for reference. Please let us know if you still have any issues, and we would be happy to address them.

FYI:

Did I answer your question? Mark my post as a solution.

[ljkeefe]

@V-yubandi-msft Yes, that's what I need and that's what I'm mostly getting. It's just 2-3 items that are not pulling the correct deadline or coming in blank.

Note: all the items on this list will have dates for multiple meetings. If my topic is "Widgets" and I need to take this to council, I first must attend committee A on a certain date to be approved to go to Committee B on another date where I receive approval to finally go to Council on a later date. For about 80-90% of the items, my formula works but for that 10-20% it either brings in the wrong deadline comment or doesn't bring anything at all. I have looked over the items in the SharePoint list and haven't been able to find any differences between the ones that work correctly and the ones that don't. 

Another thing to note: This is all pulling from the same SharePoint list, the distinguisher is the Status Column. The Status options are "Submitted for Approval", "Approved", "Deadlines". I filter views for staff so they can just view meeting memo deadlines, all other items that they submit for leadership approval is set to Submitted for approval which has an approval workflow OR once leadership approves it, it turns to Approved.

I hope that makes sense and brings more context to what I'm trying to accomplish. 

[V-yubandi-msft]

Hi @ljkeefe ,
My apologies for the oversight. I forgot to attach the PBIX file in my previous response. I've now attached it, so please take a look and let me know if it provides the clarity you need.

Thank You.

[ljkeefe]

@V-yubandi-msft When I try it on mine file, I get the following error: The expression refers to muliple columns. Multiple columns cannot be converted to a scalar value.

[V-yubandi-msft]

Hi @ljkeefe ,

We tested the scenario based on your description, and it seems to be working fine on our end. To assist you better, could you please share a sample dataset? This will help us reproduce the issue accurately and provide you with a working PBIX file.

Thank You.

[ljkeefe]

@V-yubandi-msft Yes, I can provide two attachments. Meeting Items are the agenda items that I need matched with deadlines and comments by Meeting Type from the Deadlines attachment.

Here are the tables: 

Meeting Items 

Deadlines 

As you will see, one topic can have up to 4 meeting dates and deadlines by meeting type.

Thank you for your help.

Linda 

[V-yubandi-msft]

Hi @ljkeefe ,

We have implemented the solution using the sample data you provided and attached the PBIX file for your review.

Could you kindly review it and let us know if the output meets your expectations or if any adjustments are needed?

I hope this is helpful.

Regards,

Yugandhar.

[ljkeefe]

@V-yubandi-msft This is similar to my original solution before I came to post in the community, except for your solution has a lot of duplicate Meeting Types for the same Topic showing different days and there are a lot of blanks. So no, it does not meet what I'm trying to do.

[V-yubandi-msft]

Hi @ljkeefe ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

[ljkeefe]

@V-yubandi-msft No, this wasn't necessarily a solution for the issue I was having. There are still sporatic gaps. 

[V-yubandi-msft]

Hi @ljkeefe ,

Thank you for your patience. It seems the formula is mostly working, but there are still some issues. 

Please double check the following.

1.  Ensure the Meeting Type values in both tables match exactly, with no extra spaces, spelling differences, or case mismatches.
2. Verify that the Meeting Date fields are stored as dates only, without any time components (like 12:00 AM).
3. if there are multiple Deadline entries for the same Meeting Date and Meeting Type. This might be causing issues when pulling a single Comment.
4. Create a simple table visual showing Meeting Date and Meeting Type from both tables side by side. This will help you identify any mismatches.

If you still face issues after these checks, consider raising a support ticket so the problem can be looked into in detail.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Regards,

Yugandhar.

[ljkeefe]

@V-yubandi-msft 

I've double checked the following:

1.  Ensure the Meeting Type values in both tables match exactly, with no extra spaces, spelling differences, or case mismatches. Values are the same, no issues.
2. Verify that the Meeting Date fields are stored as dates only, without any time components (like 12:00 AM). A date only column, both are using Short Date. 
3. if there are multiple Deadline entries for the same Meeting Date and Meeting Type. This might be causing issues when pulling a single Comment. Create a simple table visual showing Meeting Date and Meeting Type from both tables side by side. This will help you identify any mismatches. Did this and no gaps, no issues.

Sounds like I need to open a ticket. Thank you for your help. 

[V-yubandi-msft]

If you receive any updates or additional guidance from your support team, please consider sharing it here  it could help other community members facing similar issues.

Regards,

Yugandhar.

[V-yubandi-msft]

Hi @ljkeefe 

Since the data is structured with deadlines and approvals in the same list, the best way to bring the Comment column into the leadership report is by using Power BI's relationships and DAX measures.

1. Connect to your SharePoint site and import the list. Duplicate the imported table 
2. Filter this new table to keep only rows where Status = Deadline
3. Keep only Meeting Date, Meeting Type, and Comment columns

Create 1to Many Relationships:

Deadlines Table[Meeting Date] - Leadership Report Table[Meeting Date]

Deadlines Table[Meeting Type] - Leadership Report Table[Meeting Type]

Create Measure for the dead line:

Deadline_Comment =

LOOKUPVALUE(

    'Deadlines Table'[Comment],

    'Deadlines Table'[Meeting Date], 'Leadership Report Table'[Meeting Date],

    'Deadlines Table'[Meeting Type], 'Leadership Report Table'[Meeting Type]

)

Create a Table Visual in Power BI & Add the columns.
Helpful Reference:

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Power Query SharePoint list connector - Power Query | Microsoft Learn

Thanks..