In the past days I was asked from random people how you can simulate the flowfields inside Microsoft Fabric when you are using bc2adls tooling (Bertverbeek4PS/bc2adls).
That is because when you export it the rough data will be exported and not the fowfields.
Those calculated fields you need to do again in your MS Fabric workspace.
There are two options to do this in Microsoft Fabric (Power Query and Data Wrangler). In both examples we are using the “Balance (LCY)” as an example. This flowfield is calculated in the following way:
If you are familiar with Excel this option is properly a well know option for you. If you choose “Dataflow (gen2)” inside MS Fabric you get the option “Power Query”. Here you can combine tables and fields like it is in Excel.
First we need to group the table “DetailedCustLedgEntry379” with the function group by:
And it will look like:
After this we can merge the field to the table “Customer18”:
And expand the columns:
Then you can add a destination and you are done after you have published it.
It looks familiar and easy to use. But if you look at the refresh rates for this simple query, it is done in 1 minute:
Another option is to do is by Python code. You can do it in a Panda dataframe or a Spark dataframe.
You can say now. He but I cannot write any Python code. There is now a very tool you can use. This tool calls “Data Wrangler”. The only code you need two write is to merge two dataframes into one.
With the above example it is like:
import pandas as pd
#load data into dataframes
df_customer = spark.read.table("businessCentral.Customer18")
df_detCustLedgEntries = spark.read.table("businessCentral.DetailedCustLedgEntry379")
# read into panda dataframe
pd_customer = df_customer.toPandas()
pd_detCustLedgEntries = df_detCustLedgEntries.toPandas()
#do a merge on fields
merge_df = pd.merge(pd_customer, pd_detCustLedgEntries, how='inner' \
, left_on=['No-1','GlobalDimension1Code-16','GlobalDimension2Code-17'] \
When you run this code it will show up in the “Data Wrangler” menu:
When you choose our dataframe “merge_df” you get the following screen:
With this screen you can do all kind of action based on the UI. In this case we want to group on our “Customer No.” and “Name” and calculate the “Amount LCY” and maybe also :
In the section below we see already the Pyhton code that is generated.
If we choose “Apply” we can already see the results:
If we click on “Add code to notebook”
We return to the notebook the code will be applied in a new section:
If we run all the code and print it we get te following results:
In Business Central it is:
And it is only runned in 15 seconds.
14 sec 515 ms for the merge and loading the data in a dataframe and
317 ms for groupby and sum.
So in this case with the notebook code you get a much better and faster result!
Want to learn more about how bc2adls works with Microsoft Fabric works read the following two blog posts:
BC2ADLS: MS Fabric enabled part 1 – Discover Microsoft Business Central (bertverbeek.nl)
BC2ADLS: MS Fabric enabled part 2 – Discover Microsoft Business Central (bertverbeek.nl)