Using a List and PowerBI for regular progress reporting

[MARudge]

Hi - I've been asked by a Moderator to post my question here as sadly the SharePoint Community couldn't come up with any suggestions. 🙂

I have built a list which contains all our major company initiatives. It is awesome and people love the card view and being able to click in and see more detail. Its only really an MVP 1 but people are finding it so easy to use they want to use it for a bit longer!

However one part doesn't work that well. 😞 I created a column for Q1 RAG and Q1 Narrative. That bit worked fine but of course I have had to create a new column for each quarter. This has caused all sorts of problems when producing PowerBI reports especially when trying to show historical narratives and RAG status. We are now moving into year 2 reporting and I'd like to find a better way to store the quarterly reports in the list and report on them in the PowerBI dashboard easily.

Has anyone else used MS Lists for regular reporting and got it to work more effectively woth PowerBI?

Many thanks

Mark

[MARudge]

Thanks - apologies but can you help me understand 'in the column well of the matrix visual (for example)'? Thanks again.

[lbendlin]

Where it says "Add data fields here"  under Columns.

[lbendlin]

Instead of creating a column for each quarter, create a column called "Quarter" and populate that as needed.  That way you have data that is usable by Power BI, and you don't have the problem of column count creep.

[MARudge]

Thanks for the thought here. However we need to be able to see each quarter as a seperate report across all initiatives and just storing it in a single column won't achieve that. 😞

[lbendlin]

Yes, it will. All you need to do is to place the quarter field in the column well of the matrix visual (for example)