Customer Insight Journeys Real Time Dynamic Forms Loader

Customer Insight Journeys Real Time Dynamic Forms Loader

In a previous post I blogged about how to break down the Form script that was exported from CI-J (Real Time). As some customers asked me about making this into a script that was the same and was dynamic based on query string parameters (parameters in the URL), I worked a bit on that and thought I’d share it here;

<div id="formdiv">

function getQueryParam(name) {
         const queryString =;
         const urlParams = new URLSearchParams(queryString);
         const queryparam = urlParams.get(name);
         return queryparam;

        const formobj = document.querySelector('#formdiv');
        const formid = getQueryParam('id');
        const formapiurl = '' + getQueryParam("orgid") + '/landingpageforms'; 
        const formcachedurl = '' + getQueryParam("orgid") + '/digitalassets/forms/' + formid;

        formobj.setAttribute('data-form-id', formid);
        formobj.setAttribute('data-form-api-url', formapiurl);
        formobj.setAttribute('data-cached-form-url', formcachedurl);
    <script src=''></script>

A few things that can be mentioned. This script expects that there will be two query string parameters:
id – the id of the form. Click on the form and copy it from the URL after the “id=”
orgid = the orgid which you can find in the PPAC or in the export of the script as I described in the previous article. If it is placed on the url: then an example of the url would be:

Finally some organizations also have problems with loading a script from external sources. I will look at that too. Mainly there are two option. Copy-paste the entire script inline into this script or copy the script file to an “internal” or recognized store with a public URL and change the src-attribute. If you move this away from referencing Microsoft, I would recommend checking their website on a regular basis to make sure it hasn’t changed.

I hope this helps out!

Calculated columns + Azure Synapse Link != true

Calculated columns + Azure Synapse Link != true

I was recently helping my colleague Ebba Linnea Nilsson with a support ticket with data not being propagated correctly from dataverse to a datalake via Azure Synapse Link. It turned out that this was all by design. A design that might not be what normal users would expect.

Calculated columns and now recently the formula columns are both very useful way of being able to calculate data in a field that is based on other fields. Common scenarios are calculations like “Weighted revenue” which is the probability multiplied by the estimated revenue for an opportunity. However, there are scenarios where you need to be aware of how these fields actually work or you might get an unwanted or unexpected behaviour.

The first thing that needs to be understood is that these column types are calculated “on-the-fly” everytime dataverse attemts to access these columns. It might seem like the data is “in the columns” but it really isn’t, it is calculated. This is a big difference from for instance rollup-columns is that those columns are calculated on a regular interval by the system, and the result is stored in the record.

What does this mean for Azure Synapse Link? Well, let’s say we have a simple calculation, that sets the value “A” into all records for this calculated column. We then enable the Azure Synapse Link which will make an initial sync and set the column in the datalake to “A”. Now we change the calculation of the rule to output “B” instead. As no records are actually changed, this will not cause any records in the datalake to be updated, hence they will all still have the value “A”. From a user perspective comparing Dynamics 365 to the datalake without any underlying understanding of how this functions, it will look like an error. Same column has different values comparing what is in dataverse with what is in the datalake.

As soon as a record is actually changed, all columns for that record will then be sent to the datalake, and hence the calculated column will be set to “B” at that time. It is hence possible, to manually or semimanually force a resync, but it would require some bulk like for instance SSIS with Kingswaysoft especially for implementations with large amounts of records.

An important question to ask, is why would you want to calculate the data in dataverse and then use it in in the datalake. If you have a propper datalake architecture it should be easier to make calculated columns/fields in the datalake/datalakehouse. If the data is calculated only for use in the datalake, I would suggest moving the calculation to the datalake.

There are, of course, scenarios when it is preferrable to have calculations in one place and reuse the output in many places. However, this understanding of what can reasonably be expected is then essential.

As for product improvements, I have added an idea on the subject, if you agree with me, please vote! Microsoft Idea (

A final note is that this type of unexpected behaviour is not limited to just Azure Synapse Link but really to any integrations based on either “modified on” or change tracking without doing periodic synchronizations. Hence I would also like to give a general warning about this.

Utilize query from Marketing reports in data flows

Utilize query from Marketing reports in data flows

Recently I needed to get the marketing insight replicated data from the data lake where it is replicated to, to Customer Insight. However, that turned out to not be very easy as the data had some formatting issues. However, I found that the Marketing reports that Microsoft have release for Power BI can be used as inspiration for how to query the data.

I am currently working on a rather long Proof of concept of Customer Insight for a customers. One of the things they wanted to see if it worked to get into Customer Insight was EmailClicked which is part of the Marketing Insight data that Marketing stores in an internal database. This can rather easily be replicated to an external datalake, with some configuration in the admin tab in Marketing. However, when I tried to connect to that data, using the Azure Datalake gen2 adapter in Customer Insight, it consistently said that there was no data. After some digging I found that the main reason for this was that the files in the datalake did not have the propper file-ending for the datalake connector to understand them. In short, they are csv-files, but do not have the filename xyz.csv. Simple enought problem, I thought but as I am not super comfortable working with datalake data, I tried to figure out some way of easier solving this issue. First I tried using the dataflow connector to ADLS gen 2 but that got the same problem. Just so you know.

Then it struck me, data flows use PowerQuery/M which is the same thing that is used in Power BI, AND Microsoft have release some marketing reports for Power BI that utilize the data in the datalake combined with dataverse. I hence opened one of these and tried to copy the entire data query part. It turned out to be more than 20 different components. Datasources, configuration, functions and more. When pasted into the Data Flow, it simply didn’t work and as usual, I didn’t get a very good errormessage. But my hunch was that maybe the logic is too complicated with too many internal connections for the data flow. If you know the limitations here, please leave a comment. This didn’t stop me, my next step was to remove all unnecessary stuff from it, like the dataverse queries, config and such, but still, it didn’t work. So I attempted to move it all into one single M-script with a hardcoded referece to EmailClicked. When I did that, it worked! This is the final result:


    Source = AzureStorage.Blobs("<datalakename>"),
    #"ContainerContent" = Source{[Name="<containername>"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(ContainerContent,{"Content", "Name", "Date modified", "Attributes"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Name] <> "model.json"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Sorted Rows", "Attributes", {"Size"}, {"Size"}),
    #"File Name column" = Table.DuplicateColumn(#"Expanded Attributes", "Name", "File Name"),
    #"Remove csv" = Table.ReplaceValue(#"File Name column","csv/","",Replacer.ReplaceText,{"File Name"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Remove csv", "File Name", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Interaction Name", "File Name"}),
    #"Transform" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Interaction Name", type text}, {"File Name", type text}, {"Size", Int64.Type}}),    
    #"Add Datestamp" = Table.DuplicateColumn(#"Transform", "Date modified", "Datestamp"),
    #"DateStampFormat" = Table.TransformColumnTypes(#"Add Datestamp",{{"Datestamp", type date}}),
    TodayFunction = DateTime.FixedLocalNow,    
    #"Add Today" = Table.AddColumn(#"DateStampFormat", "Today", each TodayFunction()),
    #"Changed TodayType" = Table.TransformColumnTypes(#"Add Today",{{"Today", type date}}),
    #"Add DaysFromToday" = Table.AddColumn(#"Changed TodayType", "DaysFromToday", each [Datestamp]-[Today]),
    #"Changed DaysFromToday" = Table.TransformColumnTypes(#"Add DaysFromToday",{{"DaysFromToday", Int64.Type}}),
    Result = Table.RemoveColumns(#"Changed DaysFromToday", "Today"),
  result2 = Table.SelectRows(Result, each [DaysFromToday] >= -180),
  FilteredByInteraction = Table.SelectRows(result2, each [Interaction Name] = "EmailClicked"),
  #"AddFileContents" = Table.AddColumn(#"FilteredByInteraction", "FileContent", each
    Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true])),
  #"ContentTable" = Table.SelectColumns(AddFileContents,{"FileContent"}),
  #"NoDataFiles" = Table.IsEmpty(#"ContentTable"),
  InteractionTable = Table.ExpandTableColumn(#"ContentTable", "FileContent", Table.ColumnNames(ContentTable{0}[FileContent])),
  #"Transformed" = Table.TransformColumnTypes(InteractionTable,{{"Timestamp", type datetimezone}}, "en-US"),
  #"Duplicated Column" = Table.DuplicateColumn(#"Transformed", "Timestamp", "Datestamp"),
  #"Datestamp" = Table.TransformColumns(#"Duplicated Column",{{"Datestamp", DateTime.Date, type date}}),
  #"RenameId" = Table.RenameColumns(#"Datestamp",{{"InternalMarketingInteractionId", "Id"}}),          
  #"keyedtable" = Table.AddKey(#"RenameId", {"Id"}, true)

I have highlighted the datalake name, container and the table. I hope this helps if you are having a similar issue!

Long term Retention – now in Preview!

Long term Retention – now in Preview!

The long awaited Long term retention feature for dataverse is now in public preview. You can read more about it on Microsoft Docs.

This is really a good functionality that many of us working with larger customers that have a lot of data, and hence paying a lot for it, have been waiting for.

It is a bit hard to test, and as it is preview, it is not recommended to be used in production. The reason is that for larger organizations, the cost of having a lot of data in non-production is usually too high to justify it.
Due to this reason, I have a ask, that if you think this is an interesting functionality, please try it out and send any feedback to Microsoft on the idea-site. I have sent quite a few, and please vote for any ideas you think are good.

The key question, is of course, what the cost for storing data in the long term storage will be. As the data is stored in an internal datalake (from what I understad), I would presume it to be closer to file-storage costs than the db-storage costs. But i guess we’ll all see soon.

Happy apping!

Data Flows with null in boolean

Data Flows with null in boolean

Working with Data Flows (Power Platform Dataflows) is a useful method of getting a variety of types of data into Customer Insight and also other places. It is very easy to work with but troubleshoot it can be rather tricky as I havn’t gotten any good error messages. In this case, that null-values in booleans aren’t supported.

Recently I was using Data Flows to pull data from the datalake which was synchronized from dataverse with Azure Synapse Link. However, when I tried to publish the data flow, I just got generic errors. This is of course rather frustrating and if you have any good ways of troubleshooting this, please leave a comment.

However, after working with this and trying different angles, I did not that some people on some forums mentioned that there could be problems if boolean columns had null values. How is this possible? Booleans shouln’t they be either true or false? I have even seen that in official documentation. That is not the case in the real world. The most common scenario when you will get null-values in booleans in dataverse, is if you for instance have a table with 1M records, then add a boolean field. You will now have 1M records with null in the new field.
It was a simple fix. Just did a simple replace of the values in Power Query/M. I also checked the actual M code to make sure that it was ok, which I think can be hard to do from just the UI.

Table.ReplaceValue(#"Step 4", null, false, Replacer.ReplaceValue, {"<fieldname>"})

The important thing to notice is that it says “null” without any quotation marks. This is important as we do not want to replace the string “null” but the cells with no data.

I hope this will help you out a small bit in your data flow quest!