Data Transform Remove Rows

[Anonymous]

Hi, Need some help with a powerquery. My application reports the following structure of data

Column1	Column2	Column3
Org: Admins
Employee: John Doe	ID:	None Assigned
	Admin	Root
	Admin	Root
Org: HR
Employee: Jane Doe	ID:	None Assigned
	Read/Write	HR
	Read/Write	HR
Org: Marketing
Employee: Tom Doe	ID:	None Assigned
	Read	Root
Employee: Jim Doe	ID:	None Assigned
	Read	Enterprise
	Read	Root

I would like to query the "Column1" for each Org: department and produce a new column with that range of users. Like below:

Org: Admins	John Doe	Admin	Root
	John Doe	Admin	Root
Org: HR	Jane Doe	Read/Write	HR
	Jane Doe	Read/Write	HR
Org: Marketing	Tom Doe	Read	Enterprise
	Jim Doe	Read	Enterprise
	Jim Doe	Read	Enterprise

Column1 will change for each report as new users are created on the system so the query must be able to identify the break. Any help is appriciated

I believe my query is similar to this thread but the systax is not working on my data: Solved: Remove all rows after specific row in Power Query - Microsoft Power BI Community

[parry2k · Accepted solution]

@Anonymous see the attached solution, tweak it as you see fit.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

worked a treat thanks