Need help with this M query please

[DanJaz85]

As per below screenshot, parameters were defined in the parameter section of Power BI.

And I have below query to get the data from SQL database via the stored procedure. When refresh the query it doesn't load the data from SP query outpout, instead load entire tables, views and functions list from the database as the output.

can someone please help me to fix the below data source. I just want to execute below SP by passing the parameters dynamically and load into Power BI. thanks

let
Source = Sql.Database("MyServer", "My_DB", [Query="EXECUTE My_DB_SP "&Text.From(WhseFrom)&","&Text.From(WhseTo)&","&Text.From(FactoryFrom)&","&Text.From(FactoryTo)&","&Text.From(DateTo)&","&Text.From(DateOffestTo)&","&Text.From(DateFrom)&","&Text.From(DateOffsetFrom)])
in
Source

@Greg_Deckler @rajendraongole1 @lbendlin @SamWiseOwl @Ritaf1983 

[lbendlin]

You need to add the single quotes around your SP parameters.

[DanJaz85]

Hi @lbendlin 

thanks for that.

I tried with single quotes too. but it didn't work either.

intead of SP, I'm trying to run the SQL query directly, but I still get below error on the parameters.

= Sql.Database("MyServer", "My_DB", [Query="SELECT
COUNT(distinct c.co_num) AS [Total Order Count],
CAST(SUM(it.uf_emdaSQMperUnit * co.qty_ordered)AS DECIMAL (18,2))AS M2,
CAST(SUM(it.uf_emdaKgMtr * uf_emdaLength * co.qty_ordered) AS DECIMAL (18,2)) AS [Weight]
FROM co_mst c WITH (NOLOCK)
JOIN coitem_mst co WITH (NOLOCK)
ON c.co_num = co.co_num
JOIN item_mst it WITH (NOLOCK)
ON co.item = it.item
WHERE
CAST(c.order_date AS DATE) = '"DateFrom"'
AND c.whse BETWEEN '"WhseFrom"' AND '"WhseTo"'
AND ISNULL(c.uf_emdaFactory,'') BETWEEN ISNULL('"FactoryFrom"','') AND ISNULL('"FactoryTo"','')
AND c.stat = 'O'
AND co.qty_ordered > 0"])

below is the error I get for above query

when I add && on the parameters, it returns below error

any help would be greatly appreciated

[lbendlin]

CAST(c.order_date AS DATE) = '" & DateFrom & "'