Filter results from SharePoint query in advanced editor

[sp_mike]

I hav a query collecting records from sharepoint lists. This gives me a lot of results. Now I need to filter the records on the current user. One field returning from Sharepoint is "SP_username".

So in de the advanced editor I would like to do somethink link. "Filter records where USERNAME() = "SP_username".

I tried to do something like "#"Filtered Rows" = Table.SelectRows(dbo_ConditionTimeline, each [COLUMNNAME] > #date(2014, 1, 1))"

This is not working in my case. I think because I have a Json result.

Can someone help me out?

[Anonymous]

@sp_mike Sounds like you are trying to apply security in the wrong area. Once you pull the data in, then you can assign a role/row level security based on the data in your model. see here

[sp_mike]

---

@Eno1978 wrote:

@sp_mike Sounds like you are trying to apply security in the wrong area. Once you pull the data in, then you can assign a role/row level security based on the data in your model. see here

---

@Anonymousactually, I do not try to filter on the records that I own or am authorised to. I try to filter on the records that I am "mentioned" in.

I try to explain with an example. In sharepoint somebody creates a listitem, one of the fields of this listItem is "Engineer". This Engineer can be anybody. In power BI I only want to see the records of which I am the engineer. And If "Joe" opens the powerBI dashboard he only wants to see the records where he is the engineer.

Thanks, Mike

[Anonymous]

@sp_mike I'll try this again, but the answer is the same. You want row level security.

Bring in the entire data set. It will have all engineers. There are multiple ways you can implement Row Level Security, either a user directly associated to a role (described in the previous link), or create a mapping table that you reference in your DAX function which validates the user coming in using the USERNAME() function in DAX. A walkthrough was done by Reza Rad here

Essentially, rather than try to filter things on the input (in Power Query) - Which won't be dynamic in the external facing report -> you need to set this up in the external facing part where any user logging in will only see their data.

[sp_mike]

@Anonymous Thanks, I'll dive into that. I thought it was only for authorization puposes did not realize it could be a solution for my problem.

Problem could be I need the pro version for this. With a large amount of users...

[Anonymous · Accepted solution]

@sp_mike RLS is going to be a "Pro" level feature, which would require all users to have a Pro license.

Based on what you are saying, I really think it is what you are asking to do. Give a look in those links, and if it works out or not comment back so we can explore further or close out the thread for others.

[sp_mike]

I figured it out.

#"FilteredTable" = Table.SelectRows(#"Removed Columns", each [HourList.Name] = "j.doe")

I have to make this more dynamic but that will not be a big problem.

[sp_mike]

However ;-(

When I try to do it in some dynamic way, I try this:

 #"FilteredTable" = Table.SelectRows(#"Removed Columns", each [HourList.Name] = (Text.End(USERNAME(), (Text.Length(USERNAME() - Text.PositionOf("\",USERNAME())))

This is not working. I'm not sure but I think te USERNAME() function does not work in power query...

Any suggesions would be very welcome