Get URL address by searching SharePoint from Power BI

[en1701d]

Hi All;

I have a SharePoint site with a search bar (I know, big shocker).  When a user enters an ID, a specific result is returned.  On occasion, multiple results are returned, but 99% just one is returned.

The ID is in Power BI as a unique column.  I'd like a second column, using the hyperlink icon and function, to be the return of the ID resulting page.  I have a manual work around by copying and pasting the URL into an excel table and using a cross reference from my main Power BI query to the Excel document table.

My concern is that if it is manually done, someone will either forget or enter the link into the wrong field.

Is there a way to automate this from Power BI since it already can pull queries from SharePoint?

For instance:

ID = BOB

SharePoint site is searched and returns a result for BOB

The resulting hyperlink is (some link): sharepointsite.com/123/bob

Thanks for the help,

Mike

[en1701d]

So I discovered that the base URL is the same.  

www.page.com/id=1234

The 1234 is the unique site.  

My idea is to hardcode the www.page.com/id= and then use the right() to pull the ID number from another column.  I am having issues with the formula though.

Value = TBL-1234

ID = 1234

formula attempted: RIGHT(ID,4)

the problem is that I am really unfamiliar with Power BI coding.  I've only used it for about 10 hours and most of that was just getting the queries to work correctly from different sources.

If the column that has TBL-1234 is call "PCR ID", what would be the correct formula to extract the data I am intersted in?

Thanks

[lbendlin]

RIGHT([PCR ID],4)

[en1701d]

Tried it and it is giving an error.  The error isn't very specific.  Maybe I am entering in the code into the wrong place.  Is that possible?

[lbendlin]

hard to tell without seeing what you are doing?  Got a screenshot?

[en1701d · Accepted solution]

Thanks for the help.  I did get some help here and I was definately entering the formula in the wrong area.  I didn't know that Power BI has 2 different coding languages and are dependant on where the formula is entered.

I was trying to enter the formula in the Power Query Editor instead of the Data screen which I didn't know existed.

Once I was in the Data screen the standard Excel like formula worked.

[lbendlin]

Use OData query operations in SharePoint REST requests | Microsoft Learn

[en1701d]

Hi,

The users are using touch screens which makes it difficult for them to use the search function on the manufacturing floor.  By "auto" linking the two, there are a reduction of possible errors.

I'm really new to Power BI.  Is there a good link I can read about ODATA queries?

Thanks

[lbendlin]

Read about ODATA querying against Sharepoint. Although not sure if that will work with Direct Query and/or dynamic query parameters.

Wouldn't it be easier to point Power BI to the sharepoint list and let the user search in Power BI?