We think these might be a bug which will mislead us about the correctness of final output data.
I try to re-produce the issue with a simple example as the attach file.
describe as below:
1. There is 2 excel files : DataPlan.xlsx and DataActual.xlsx.
And we want to produce the final data to combine both plan and actual data, and with common field : 'Plan Amount'
(1-1) Plan Data
(1-2) Actual Data (Only PRJX earn amount 800)
(1-3) Expect Final Data ("plan amount" for each record)
2. The Prep Flow :
(2-1) For Plan Data, Add 2 Fields: "Version"='Plan' and "Plan Amount" = [Amount]
(2-2) For Actual Data, Using Plan Data Left Join Actual Data,
Let [Plan].[Amount] (which is [Amount] now) be "Plan Amount"
[Actual].[Amount] (which is [Amount-1] now) be "Amount"
And Add "Version" = 'Actual'
(2-3) Then Union above data
And it looks perfect in Final Step and Output Step in prep.
For Version = 'Actual', the (Amount / Plan Amount) for PRJX is (800 / 1000) , for PRJY is (0 / 500), that is what we want.
But the back-end algorithm of "Run Flow" might has a little different from the prep preview data.
After we run flow to produce the output data, in any format (csv or hyper). the result will be:
The "Plan Amount" is wrong and be the same as "Amount" for Version Actual.
It's very strange that real output data is different from preview data in prep.
After many try and error,
we found the key point might be "to rename to the same field name which is removed before".
There is 2 workaround to avoid the step:
[Workaround A] : Plan data Right Join Actual Data
In this join type, [Amount-1] is [Plan].[Amount]
and we just need to rename [Amount-1] to [Plan Amount]
[Workaround B] : avoid to rename the same name.
for example, rename to new field name as AMT
Even we know the key point and how to avoid the situation.
We still hope it can be fixed in the future update, to let real output be the same as what we see in prep preview.
(Because the situation is hard to be discovered in the complex flows for who believe prep preview data is correct.)