Forum Discussion
Need help with this M query please
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
3 Replies
You need to add the single quotes around your SP parameters.
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
CAST(c.order_date AS DATE) = '" & DateFrom & "'
