Dataflow Gen2 refresh issue

[pws_flick]

Hi All, 

I have created a Dataflow Gen2 which is picking up data from the 'account' entity in Dynamics 365. 

The M-Code for this query is as follows:

let
    Source = CommonDataService.Database(
        "xxxxxxxxx.crm6.dynamics.com", 
        [CreateNavigationProperties = false]
    ),

    // Grab the "account" table
    #"Navigation 1" = Source{[Schema = "dbo", Item = "account"]}[Data],

    // Keep desired columns
    #"Selected Columns" = Table.SelectColumns(
        #"Navigation 1",
        {
            "accountid",
            "primarycontactid",
            "primarycontactidname",
            "name",
            "customertypecode",
            "description",
            "emailaddress1",
            "emailaddress2",
            "emailaddress3",
            "telephone1",
            "telephone2",
            "telephone3",
            "createdon",
            "createdby",
            "createdbyname",
            "modifiedon",
            "modifiedby",
            "modifiedbyname",
            "ownerid",
            "owneridname",
            "parentaccountid",
            "parentaccountidname",
            "address1_addresstypecode",
            "address1_name",
            "address1_primarycontactname",
            "address1_composite",
            "address1_line1",
            "address1_line2",
            "address1_line3",
            "address1_city",
            "address1_stateorprovince",
            "address1_postalcode",
            "address1_country",
            "address1_latitude",
            "address1_longitude",
            "industrycode",
            "bcbi_companyid",
            "bcbi_companyidname",
            "businesstypecode",
            "defaultpricelevelid",
            "defaultpricelevelidname",
            "exchangerate",
            "fax",
            "masteraccountidname",
            "masterid",
            "msa_managingpartnerid",
            "msa_managingpartneridname",
            "msa_managingpartneridyominame",
            "msdyn_billingaccount",
            "msdyn_billingaccountname",
            "msdyn_primarytimezone",
            "msdyn_salestaxcodename",
            "msdyn_serviceterritory",
            "msdyn_serviceterritoryname",
            "msdyn_workorderinstructions",
            "originatingleadid",
            "originatingleadidname",
            "paymenttermscode",
            "statecode",
            "statuscode",
            "territoryid",
            "territoryidname",
            "transactioncurrencyidname",
            "utcconversiontimezonecode",
            "vel_abn",
            "vel_accountmanagercrm",
            "vel_accountmanagercrmname",
            "vel_accountmanagercrmyominame",
            "vel_accountmanagersales",
            "vel_accountmanagersalesname",
            "vel_accountmanagersalesyominame",
            "vel_acn",
            "vel_acquisitioncode",
            "vel_allocatedportalusers",
            "vel_allocatedportalusers_date",
            "vel_allocatedportalusers_state",
            "vel_autonumber",
            "vel_bpaycrnfol",
            "vel_businesskey",
            "vel_certificateofcompletionexemption",
            "vel_chemicalregister",
            "vel_childaccountallocatedportalusers",
            "vel_consolidatedinvoice",
            "vel_customerdivision",
            "vel_customerponumber",
            "vel_default_paymentmethod",
            "vel_default_paymentmethodname",
            "vel_division",
            "vel_edicust",
            "vel_errordetails",
            "vel_folbillto",
            "vel_folbilltonumber",
            "vel_follocation",
            "vel_follocationnumber",
            "vel_foreign",
            "vel_lastservicedate",
            "vel_mastergrp",
            "vel_mastergrpname",
            "vel_oldcustomernumber",
            "vel_oldpropertynumber",
            "vel_paymentgateway_customerid",
            "vel_paymenttype",
            "vel_picategory",
            "vel_picategoryname",
            "vel_portalinvitationresult",
            "vel_portalinvitationsenton",
            "vel_portalinvitationstatus",
            "vel_portallink",
            "vel_registered",
            "vel_remainingportallicenseallowance",
            "vel_reprtgrp",
            "vel_reprtgrpname",
            "vel_showstripesyncbutton",
            "vel_specialtermsandconditions",
            "vel_spnumber",
            "vel_totalauthorizedportallicenses"
        }
    )
in
    #"Selected Columns"

I have setup incremental refresh as follows:

The data is being written to a table (dbo.account) in my Fabric Data Warehouse with the same columns as the query. 

However, whenever I try to publish this dataflow - it will generate this error when it tries to refresh (fails on the account_WriteToDataDestination step): 

account_WriteToDataDestination: There was a problem refreshing the dataflow: 'Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: The column 'Data Source Type' of the table wasn't found. Details: Reason = Expression.Error;ErrorCode = 10224;Detail = Data Source Type;Microsoft.Data.Mashup.Error.Context = User'. Error code: Mashup Exception Expression Error. (Request ID: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx).

This seems to indicate that it's looking for a column named 'data source type' ??? I'm thoroughly confused. There is no column named this in the source table or the destination table... 

I have also tried setting the CreateNavigationProperties to 'true' rather than 'false' - but this gives the same error. 

any help / guidance is greatly appreciated! 

[v-kpoloju-msft · Accepted solution]

Hi @pws_flick,

Thank you for reaching out to the Microsoft Fabric Community Forum.

We sincerely apologize for the inconvenience. After reviewing the issue of Dataflow Gen2 refresh issue, here are a few steps that may help resolve the issue.

• Ensure the schema from Dynamics 365 matches the dataflow structure. Even small discrepancies like field names or types can cause issues. Review both schemas for alignment.
• Specifically, check if the Data Source Type column exists in the source and is correctly mapped. Ensure the partitioning column e.g., date or ID is correctly set up and recognized for the incremental refresh. Properly configure metadata like data source type to avoid errors.
• Remove any custom steps or transformations in the dataflow temporarily and rerun it. This can help identify if a specific step in the M code is causing the error.
• If the error continues, try creating a new dataflow from scratch using the defaults without adjusting the M code. This can help determine if there is a deeper configuration issue.

Also, please go through the below following link for better understanding:

Dataflow Gen2 refresh - Microsoft Fabric | Microsoft Learn

Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

If this post helps, please give us Kudos and consider accepting it as a solution to help other members find it more quickly. Also, if you have any further queries, please feel free to reach out to us, and we will guide you accordingly.

Thank you.

•  

[anon97242]

I encountered a similar issue, removing comments and whitespace in the M-Code seemed to fix it. I see you have some comments in your example.

It seems in some cases whatever transformations/generation is happening on the PowerQuery in the "Mashup" sometimes results in bad code. 

Unfortunatley the resulting M-Code is almost unreadable in our case so we need to store it seperately for development and then remove comments format before putting changes back into Gen2.

Worth a shot. 

[v-kpoloju-msft · Accepted solution]

Hi @pws_flick,

Thank you for reaching out to the Microsoft Fabric Community Forum.

We sincerely apologize for the inconvenience. After reviewing the issue of Dataflow Gen2 refresh issue, here are a few steps that may help resolve the issue.

• Ensure the schema from Dynamics 365 matches the dataflow structure. Even small discrepancies like field names or types can cause issues. Review both schemas for alignment.
• Specifically, check if the Data Source Type column exists in the source and is correctly mapped. Ensure the partitioning column e.g., date or ID is correctly set up and recognized for the incremental refresh. Properly configure metadata like data source type to avoid errors.
• Remove any custom steps or transformations in the dataflow temporarily and rerun it. This can help identify if a specific step in the M code is causing the error.
• If the error continues, try creating a new dataflow from scratch using the defaults without adjusting the M code. This can help determine if there is a deeper configuration issue.

Also, please go through the below following link for better understanding:

Dataflow Gen2 refresh - Microsoft Fabric | Microsoft Learn

Incremental refresh in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

If this post helps, please give us Kudos and consider accepting it as a solution to help other members find it more quickly. Also, if you have any further queries, please feel free to reach out to us, and we will guide you accordingly.

Thank you.

•  

[pws_flick]

Hi @v-kpoloju-msft , 

Going through each of these:

Ensure the schema from Dynamics 365 matches the dataflow structure. Even small discrepancies like field names or types can cause issues. Review both schemas for alignment.

Per lists above, the column names and order in source match those in the destination table. Likewise with data types.

Specifically, check if the Data Source Type column exists in the source and is correctly mapped. Ensure the partitioning column e.g., date or ID is correctly set up and recognized for the incremental refresh. Properly configure metadata like data source type to avoid errors.

There is 100% definitely no column with this name in either source (dynamics 365 account entity) or the desination table.

Remove any custom steps or transformations in the dataflow temporarily and rerun it. This can help identify if a specific step in the M code is causing the error.

There are literally no transformation steps in this dataflow. It is basically just what you see in the M-Code above. 

If the error continues, try creating a new dataflow from scratch using the defaults without adjusting the M code. This can help determine if there is a deeper configuration issue.

I am going to try creating a new dataflow and manually delete all the unnecessary columns from source, rather than selecting them in M-Code. 

Given Dynamics entities contain hundreds of columns of 'dynamics boilerplate' - this will be a frustratingly slow process 😂 Nonetheless, I think it's my only option at this point. 

If this doesn't work, I think I will just abandon Dataflow Gen 2 and try an alternative approach haha

[v-kpoloju-msft · Accepted solution]

Hi @pws_flick,

Thank you for contacting the Microsoft Fabric Community.

We apologize for the delayed response. After reviewing the details you provided, here are a few workarounds that might help resolve the issue. Please follow the steps below:

• Please consider using Dataflows Gen1 instead of Gen2. Gen1 operates under a different execution model and may not enforce schema validation as strictly as Gen2, which could help resolve the problem.
• If the incremental refresh is failing due to metadata issues, try switching to Direct Query mode for Dynamics 365. This method dynamically retrieves the schema, reducing the chances of mismatches and ensuring more reliable data retrieval.
• Use API pagination and field selection in Dynamics 365. API responses may impose row limits or exclude certain fields by default. Ensuring that all necessary fields are accessible and using a custom FetchXML query for data retrieval can help address issues related to missing data.
• Instead of manually adjusting the partitioning column or data source type, consider reconfiguring the incremental refresh from scratch in Power BI. Refreshing the setup and allowing Power BI to reconfigure the data can sometimes identify and resolve hidden issues related to partitioning and schema mapping.
• The issue might also be due to missing or incorrect references and relationships. Please check the relationships between the "account" entity and other entities in Dynamics 365, such as primarycontactid and ownerid, to ensure they are correctly configured.

If this post helps, then please give us 'Kudos' and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[v-kpoloju-msft]

Hi @pws_flick,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @pws_flick,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @pws_flick,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If my answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.