Power BI unable to properly read lines of Data which have multiple corresponding rows of data

[Anonymous]

Hi all,

I'm hoping someone may be able to help with this and hopefully I am placing this in the correct area (new user to this community). I have been given some school data regarding Special Educational Needs and there relevant provisions. In many cases students may have multiple provisions to help them. Please do also be aware that the data I am working with is entirely test data as I work for a support unit where we have test data in our databases, so none of the data included in any screenshots is live data.

As a result, the data in Excel (pulled from an MIS System called SIMS) displays the data with the student's name in one row and then all of the relevant data in this row as well, which is fine. However if the student has multiple of these SEN Provisions the other provisions are then displayed in the rows below with a date of when they were started etc. and no other data. The data itself isn't necessarily important, as within schools and SIMS data, there are lots of areas which do this, so it could apply to many forms of data.

The thing I would like Power BI to be able to do is to be able to recognise that where the name field is blank in a row, the corresponding provision etc. then applies to the first name above it reaches. Is there any way to do this.

The only other option is to have SIMS (the MIS) to replicate the students names so that they can then correspond with their relevant provision etc. However doing this still causes issues as it duplicates the names and affects overall figures etc.

I have also attached a copy of the data in Power BI below so you can see exactly what I mean by this.

SEN Provision Test Data

Hopefully this all makes sense and you may be able to help. If you have any questions please do not hesitate to message me.

Thanks,
Luc

[Nathaniel_C]

Hi @Anonymous ,

So if I understand you correctly you want the name above a blank, or blanks to fill in.  In Power Query, highlight the column with the names,go to the Transform tab, select Fill, and choose Fill Down.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,

Later when you are doing counts etc, you can  use DISTINCTCOUNT.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel