Crystal Report to Power BI query

pankajj — Wed, 13 Nov 2019 15:23:49 GMT

Dear community,

I have an existing report done with Crystal Report.

While converting it to Power BI, I am stuck at the following Formula:

local stringvar warehouse := "";

if {Command.ABD_ORDER_POINT} - {Command.ABD_QIS} + {Command.ABD_QID} > {Command.ABDNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ABD*" else warehouse := warehouse + ", *ABD*")
else if {Command.ABD_ORDER_POINT} > 0 then
(if warehouse = "" then warehouse := warehouse + "ABD" else warehouse := warehouse + ", ABD");

if {Command.ALS_ORDER_POINT} - {Command.ALS_QIS} + {Command.ALS_QID} > {Command.ALSNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ALS*" else warehouse := warehouse + ", *ALS*")
else if {Command.ALS_ORDER_POINT} > 0 then
(IF warehouse = "" then warehouse := warehouse + "ALS" else warehouse := warehouse + ", ALS");

if {Command.ALSVC_ORDER_POINT} - {Command.ALSVC_QIS} + {Command.ALSVC_QID} > {Command.ALSVCNetQty} then
(IF warehouse = "" then warehouse := warehouse + "*ALS-VC*" else warehouse := warehouse + ", *ALS-VC*")
else if {Command.ALSVC_ORDER_POINT} > 0 then
(IF warehouse = "" then warehouse := warehouse + "ALS-VC" else warehouse := warehouse + ", ALS-VC");

if isnull({Command.NEXT_ORDER_REQUIRED_BY}) = false then
(IF warehouse = "" then warehouse := warehouse + "*" + (IF {Command.DEMAND_WHSE} = "UNIVERSAL" THEN "C2" ELSE {Command.DEMAND_WHSE}) + "*" else warehouse := warehouse + ", *" + (IF {Command.DEMAND_WHSE} = "UNIVERSAL" THEN "C2" ELSE {Command.DEMAND_WHSE}) + "*");

warehouse

I was able to convert the query to run in Power BI as below however Power BI is not able to recognize 'warehouse'

if ([ABD_ORDER_POINT] - [ABD_QIS] + [ABD_QID]) > [ABDNetQty] then
if warehouse = "" then warehouse & "*ABD*" else warehouse & " *ABD*"
else if [ABD_ORDER_POINT] > 0 then
if warehouse = "" then warehouse & "ABD" else warehouse & ", ABD" else

if ([ALS_ORDER_POINT] - [ALS_QIS] + [ALS_QID]) > [ALSNetQty] then
if warehouse = "" then warehouse & "*ALS*" else warehouse & ", *ALS*"
else if [ALS_ORDER_POINT] > 0 then
if warehouse = "" then warehouse & "ALS" else warehouse & ", ALS" else

if ([ALSVC_ORDER_POINT] - [ALSVC_QIS] + [ALSVC_QID]) > [ALSVCNetQty] then
if warehouse = "" then warehouse & "*ALS-VC*" else warehouse & ", *ALS-VC*"
else if [ALSVC_ORDER_POINT] > 0 then
if warehouse = "" then warehouse & "ALS-VC" else warehouse & ", ALS-VC" else

if [NEXT_ORDER_REQUIRED_BY] <> null then
if warehouse = "" then warehouse & "*" & (if [DEMAND_WHSE] = "UNIVERSAL" then "C2" else [DEMAND_WHSE]) & "*" else warehouse & ", *" & (if [DEMAND_WHSE] = "UNIVERSAL" then "C2" else [DEMAND_WHSE]) & "*"
else
warehouse

Looking forward for a quick help from experts in our PBI Community.

Thank you very much in advance.

Pankajj

Re: Crystal Report to Power BI query

edhans — Wed, 13 Nov 2019 23:17:32 GMT

"warehouse" is a variable Crystal is building. Power Query doesn't work that way. I think you'll need to rebuild it from the ground up to get the same result. It would be like copying and pasting in a formula from Excel or a SQL statement. You need to adjust it to the language you are using, which is M in this case.

Re: Crystal Report to Power BI query

pankajj — Thu, 14 Nov 2019 15:44:55 GMT

@ edhans,

Thank you very much Edhans!

Really appreciate your observation and recommendations.

Can you please suggest how to alter the formula.

Best regards,

Pankajj

Re: Crystal Report to Power BI query

edhans — Thu, 14 Nov 2019 17:21:47 GMT

I'd start one piece at a time. Create a new formula in Crystal, and do one small part. Then replicate that in Power Query. Then keep going until you get it to return the same answer. I've had to redo some Crystal Reports in Power Query/Power BI, and it is a tedious conversion. There is no automated way to do it that I am aware of.

topic Crystal Report to Power BI query in Power Query

Crystal Report to Power BI query

Re: Crystal Report to Power BI query

Re: Crystal Report to Power BI query

Re: Crystal Report to Power BI query