Multiple Assigned Solicitors in Power BI

Leah Heit

I'm using Query API to create a power automate flow that exports queries every night to upload into power bi. I'm trying to figure out a good way to export constituents and their assigned solicitors, but I'm having trouble when a constituent has more than 1 assigned solicitor. I want to have 1 unique row per constituent. I used to use export where I could have multiple columns per constituent for multiple solicitors, but with query API I have to export directly from query. Is there a good way to do this? Also, is it common for constituents to have more than one solicitor assigned?

Alex Wong

@Leah Heit
You can take a look at this post, which gather data of assign solicitor in PA use in BI.

https://community.blackbaud.com/forums/viewtopic/586/70826

To answer your question, you want 1 row per constituent, that is not a function of Query as you are aware, however, Export isn't better, at least not in this case. So the question is how do you want to model your data?

A query that produce multiple rows for one constituent (per assign solicitor per row) can easily be better than using Export when you have to specify exactly how many solicitor to export and they come in “column”. Power BI will have a terrible time “consuming” this columnized data for data modeling. Instead, the per row is a assign solicitor will help you model in Power Query. You can filter as needed on “primary” (i do not know how your database's data is done, so can't give more advice than this), assuming there is only one primary solicitor, then you will achieve one constituent per row for data modeling.