Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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:
All I need to do, was to remove the last step where the function is invoked. Arg........
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |