Importing Table-Valued Function in SQL to Power BI

[markefrody]

Hi,

I have table-valued function in SQL that I wish to import in Power BI. Problem is I cannot directly import the entire table-valued function to Power BI. It prompts me to input the start and end date, which will make my data static.

The table-valued function in SQL is a bit complicated. Table-valued function name: dbo.EquipmentStatusDuration

The table-valued function works wherein when it takes two parameters “StatusChangeDateFinal” and “StatusDateFinal” into consideration.

For example if I used Feb 21,2019 (02-21-2019) and March 3,2019 (03-05-2019) as my search parameters. It will display all entries wherein these dates fall into:



It will then compute, the minutes (SegmentMinutes) and second (SegmentSeconds) per each of the entry for the Feb 21, 2019 to March 3, 2019 dates ONLY. It will disregard the date prior or after dates.

For example: if I used Feb 21,2019 (02-21-2019) and March 3,2019 (03-05-2019) it will show the below results. We will use the below circled entry:

In the highlighted row entry, the date range of ONLY Feb 21, 2019 to March 3, 2019 is taken into consideration. It does not include dates before Feb 21, 2019 and dates after March 3, 2019. It will only calculate the number of minutes and seconds for date range of ONLY Feb 21, 2019 to March 3, 2019.

Please let me know if this function is possible to be imported by Power BI using the same logic used in the table-valued fuction in SQL? Appreciate your kind response. Thanks.

P.S.

Not sure if this will help, but below is the SQL code used to run the query:
SELECT EquipmentIdentifier, VehicleType, Year, MFGName, ModelName, StatusChangeDateFinal, StatusDateEnd, StatusEnd, SegmentMinutes, SegmentSeconds
FROM dbo.EquipmentStatusDuration('02-21-2019', '03-05-2019') AS EquipmentStatusDuration_1
WHERE (StatusEnd = N'In Service')

[otiliagaino]

Hi, can anyone be of some help looking at my current issue with dynamic parameters to table valued functions. Everything is described at https://community.fabric.microsoft.com/t5/Power-Query/Power-Query-Date-Parameter-Binding-to-my-Direc....

Any help would be really appreciated!! Thanks in advance

[venal · Accepted solution]

@markefrody 

I tried to re-pro the issue from my end and it is showing that static, no option to change the input parameters.

I tried two ways, 

1. After selecting the function, passed the input parameter values before loading.

2. By using the "Advanced Options", copied the function select statement.

SELECT * FROM DBO.EMPHIREDATE('01/01/1981','12/31/1981')

Hoping that this is be design now, Please post your idea in "Power BI Ideas Forum Page".

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[LeafySeaDragon]

Has this issue been fixed?  I am at a loss as to why this and others have been marked as "solved" with no viable solutions noted. 😕

[markefrody]

Thanks for checking @venal. I'll probably replicate how the table-valued function is constructed in SQL to Power BI. 

Thank you again for your support.