How to: Business Central Flowfields inside Microsoft Fabric

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:

Power Query

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:

Data Wrangler

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'] \
    , right_on=['CustomerNo-9','InitialEntryGlobalDim1-21','InitialEntryGlobalDim2-22'])

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 :