Fill column with SQL Server Function

[Anonymous]

Hi,

for my problem I found this:

https://community.powerbi.com/t5/Desktop/SQL-Functions-in-Power-BI/m-p/235426#M104804

But it seems, it is no longer working.

As Source I have a SQL table, I only use 2 columns from it.: ordID and oLineID

Next I want to add a custom column: color 

with gets its value from a SQL Server Function with Parameters ordID and oLineID.

First Step:

ordID	oLineID
309984	1278640
309985	1278641
309987	1278645

How it should look final like this:

ordID	oLineID	color
309984	1278640	green
309985	1278641	blue
309987	1278645	yellow

This is the SQL Server Function:

CREATE FUNCTION MAU_BI.fnGetFarbe
(
@ordID int,
@oLineID int
)
RETURNS nvarchar(max)
AS
BEGIN

DECLARE @strReturn nvarchar(255)

select @strReturn = ( select optDescription
from Product.Options

inner join OrderLineOptions on OrderLineOptions.optID = Product.Options.optID and optoID = 4
inner join OrderLines ol on ol.oLineID = OrderLineOptions.oLineID
where ol.ordID = @ordID and ol.olnID = @oLineID)

RETURN(@strReturn);
END

It was working a few years back, so it should still work. I was a shame, the M Source was not included in the original post.

Sven

[camargos88 · Accepted solution]

Hi @Anonymous ,

You can create a query calling this SQL Function and transform it in a Power Query Function. After that just invoke this new power query function in your table passing both parameters.

This link can help you:

https://eriksvensen.wordpress.com/2020/04/29/powerquery-using-sql-scalar-value-functions-in-powerquery/

[Anonymous]

All I need to do, was to remove the last step where the function is invoked. Arg........