Want to extract tables from User Defined Function in Power BI Desktop

[svcpowerbi]

Hi,

I have a UDF called udf_IV_Opp that I have returned in Power BI desktop via SQL Server (Import model).

I also have another table called v_rpt_Opp which contains Opp_ID

Opp_ID also exists within the the UDF as well as Quantity.

How do to I extract this data from the UDF in the form of tables so I can create a relatioship with v_rpt_Opp in the model view?

Thanks,

Sabil

[hackcrr]

Hi, @svcpowerbi 

Since Power BI cannot import UDF results directly, you need to create SQL views or tables that encapsulate the UDF results. The following is an example of how to create a view:
Use the following code to create a SQL view:

CREATE VIEW vw_udf_IV_Opp AS
SELECT *
FROM dbo.udf_IV_Opp()

Or, if you wish to create a table:

SELECT *
INTO dbo.tbl_udf_IV_Opp
FROM dbo.udf_IV_Opp()

Open the Power BI desktop. Click Get Data-> SQL Server>> Enter the server name and database name.
If you created a view, select vw_udf_IV_Opp. If you created a table, select tbl_udf_IV_Opp. Load the selected view or table into Power BI. Go to Model view and locate the v_rpt_Opp table and the newly imported vw_udf_IV_Opp (or tbl_udf_IV_Opp).Opp_ID Drag and drop fields from vw_udf_IV_Opp (or tbl_udf_IV_Opp) to field v_rpt_Opp in the Opp_ID. make sure that the Relationship Base setting is correctly, usually a one-to-many or many-to-one relationship.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.