Tips to Export Data To Data Lake From MS Dynamics 365 CE And Use It In Power BI.

Integrating Dynamics 365 with Azure Data Lake allows you to continuously sync operational data for cost-effective big data analytics, AI workloads, and custom reporting.

The datatypes handshaked between Datalake to Dataverse is explained below:

DataType in Dataverse	DataType in Datalake
Text	Varchar
Number	Decimal/Integer as needed
Date	Date
Lookup	Guid
Choice	Integer Value of option set
Boolean	true/false

Thus to send any lookup values, you need to store the masters in a table in Datalake and send the respective guids instead of the value. Eg; City master. Also when you take back data from Dataverse to Datalake, you would get the unique guid only and not the value itself.
To know more in detail, here is the link. Here customizing the batch size is explained in detail too, do have a read.

Power Tip: To avoid backtracking again, you can create Power Fx columns in dataverse to store the text values and take them directly for values.

Please Note: The Dynamics data types  AttributeType.CalendarRules, AttributeType.MultiSelectPicklist, and AttributeType.PartyList aren’t supported.

While fetching data from the entire entity using the Synapse pipeline sometimes the execution intermittently fails with various errors.

Problem Statement: One such scenario we faced was that some columns were missing when we imported the schema or preview data even though the attributes were present on the FetchXML.

Cause: On inspection, we found that this issue is by design, because Data Factory and Synapse pipelines are unable to show columns that contain no values in the first 10 records.

Solution: Explicit Mapping is what we need here- firstly map the columns which you need

While configuring Explicit Mapping, we manually added the mapping for the missing columns along with their corresponding data types into the JSON definition of the Copy activity. This helped resolve the issue, and the data was successfully fetched without missing the column.

Once you have done it, you will be able to see all the required columns in your target entity.

For more errors, you can read here.

Discussion: The intermittent nature of the issue is primarily influenced by resource availability within the Dataverse execution engine. When sufficient resources are available, the query can be processed successfully and the results are returned as expected. However, during periods of higher load or resource constraints, the same query may fail due to the increased processing requirements associated with the complex entity structure.

The observed error refers to the following document: Query anti-patterns (Microsoft Dataverse) – Power Apps | Microsoft Learn

The guidance in the document suggests
1. Minimizing the number of selected columns to improve query performance.
2. Minimize the number of selected logical columns
3.Avoid leading wild cards in filter conditions
4.Avoid using formula or calculated columns in filter conditions
5.Avoid ordering by choice columns
6.Avoid ordering by columns in related tables
7.Avoid using conditions on large text columns

In our case,

• It was identified that the issue is related to the presence of multiple complex data types within the entity being queried.
• Several of the columns contain reference values and relationships to other entities. As a result, the overall query response becomes significantly larger and more resource-intensive to process.
• Due to the complexity and size of the generated result set, the query may fail under certain conditions with the observed error.

Additionally, this behavior is not specific to Azure Data Factory. Any client or tool attempting to retrieve the same dataset using a similar query pattern may encounter the same issue because the limitation originates from the query execution and result generation process within Dataverse.

Another Use Case: For large organizations , managing and analyzing large volumes of audit data can become increasingly complex and costly. We can export Audit Log from Dataverse for compliance and governance. It is explained here.

Now, Azure Synapse Link allows direct sync with Azure Synapse Analytics.

Happy Reporting!
Hoping the above explanation helps you if you are trying to import/export from Datalake.

& the Power Quote of the Day is:

“Sometimes the strongest thing you can do is begin again, wiser than before. The power of Restart lies in believing that your next chapter can be better than the last.”

Power Automate & One Drive – Part 2: To list Files in a desired dynamic folder via name

Business Requirement: To List files in a desired dynamic folder

Problem Statement: If you have the folder name{dynamic} stored in a variable, and if you use the variable in the Folder Path to list files, the system will throw you an error of Invalid Request.

We had defined it in variable like this:

Mantra: This is because if you do a “Peek Code” in list files, it actually uses an ID to traverse through folders not just the path.

which if you select the desired folder and run it, it looks like below:

Here is a snippet view of it:

If you use this ID in place of the folder path in the variable and place it in list files folder property,

You will get the desired result:

But how to get this ID dynamically every time basis on the name?

Here is where this Blog will come in handy and all you have to do is , use the action “Get File Metadata using path” and pass the folder path dynamic value here. What it does is it gives you the ID property which you can then utilize in the List Files in folder action as demonstrated below:

Voila! This will list all the files in the required folder successfully.

Bonus Points: Along with the ID, it gives a lot of other properties which you can use for further condition checks based on your logic namely “name of file/type of file/is folder or not/last modified time” and so much more.

Happy Exploring!
Hoping the above explanation simplifies things for you.

& the Power Quote of the Day is:

“Power is given only to those who dare to lower themselves and pick it up. Only one thing matters, one thing; to be able to dare!” – Fyodor Dostoevsky

Power Automate & One Drive – Part 1: Creating Folders

Business Statement: To create a folder in OneDrive with a desired name

Solution: If you look at the actions for “OneDrive for Business” connector, you will see till date the “Create Folder” action has not been introduced but for such a ubiquitous functionality, Microsoft must have created a way right. Well! Yes we have a workaround and it is as follows:

Mantra: When you create a file using a folder path, if the folder exists, then it will create the file inside the folder; if not, it will create the folder plus the file too.

Solution: Lets see how it is done! If you have the desired file, then add it directly, and if no file is there, create a dummy file and once the folder is created, delete this file again.

Steps: We are creating a dummy file here where the desired file location with name is stored in the variable which can even be dynamic. Use the “Create File” action and put in the file name and content under the desired folder path

Results: Voila! The folder is created

Bonus Points: Using the same method, you can create a hierarchy of folders too by putting the folder names separated by front-slash.

Results: The hierarchy is created as below

Lastly, just delete the dummy file using Delete file action and by passing ID to it from the previous step:

& it is done! You can create the entire folder structure with or without the file here.

Check out the upcoming Blogs for some more interesting mantra’s around it.

Hope it helps!

& the Power Quote of the day is:

“Power is like being mannered, if you have to tell people you are, you aren’t, it simply shines through you.”

How to list related records of a table with N:N relationship in Power Automate

Business Requirement: We have two tables- Cluster Identifier & Primary Skills having N:N relationship between them. The requirement was as soon as a Cluster Identifier is selected, we had to list all the related skills and store it in a separate field concatenated with commas.

Solution: We will write a Power Automate as follows:

Step 1: Get a Row By ID to get the Cluster Identifier selected.

Step 2: List all Primary Skills. This we can do by listing all records of the intermediate table built to store all the values.

Theory: This table is not exposed on the frontend but can be accessed by using the Entity Name of the N:N relationship as shown in the image below and concatenating the word “set” at the end with the cluster identifier record id equalization.

If you check the JSON output of this step, it will come like this:

 "value": [
      {
        "@odata.type": "#Microsoft.Dynamics.CRM.wip_wip_clusteridentifier_wip_primaryskill",
        "@odata.id": "https://orgfbaa0000.crm.dynamics.com/api/data/v9.1/wip_wip_clusteridentifier_wip_primaryskillset(*5a70ef1-6cbf-ee11-9079-002248d5dd0*)",
        "@odata.editLink": "wip_wip_clusteridentifier_wip_primaryskillset(*5a70ef1-6cbf-ee11-9079-002248d5dd0*)",
        "versionnumber@OData.Community.Display.V1.FormattedValue": "13,472,112",
        "versionnumber@odata.type": "#Int64",
        "versionnumber": 13472112,
        "wip_clusteridentifierid@odata.type": "#Guid",
        "wip_clusteridentifierid": "23364903-99ba-ee11-907a-002248d5dbc4",
        "wip_primaryskillid@odata.type": "#Guid",
        "wip_primaryskillid": "ff22d2c9-5cbf-ee11-9079-002248d5d19f",
        "wip_wip_clusteridentifier_wip_primaryskillid@odata.type": "#Guid",
        "wip_wip_clusteridentifier_wip_primaryskillid": "05a70ef1-6cbf-ee11-9079-002248d5dd06"
      }

Thus we see in this table, it stores the Guid of both Cluster Identifier & Skill table for all related skills.

Step 3: Then do a Apply to Each in these skills and get the Primary Skill table name using Primary skill id and Append to an array variable

Step 4: Lastly when out of the loop, use the compose step with expression join(variables(‘Primary Skill ID’), ‘,’)

Tip: Usually when we use “concatenate” to append the skills to add a comma towards the end of string, at the end we have to again remove the last one, so this “join” option is a better feature where we can use the outputs directly.

Hope it helps!

& the Power Affirmation for the day is:
“I am constantly growing and evolving into a better person”

How to place Option Set values as Legends on Power Apps Charts

Problem Statement: The client had to show how many people are attending the conference from their office locations in India.

Table Structure: The Employee ID, Name, Contact, Email, City, Attending?[Yes/No/Not Sure] are all stored in a table where Attending is the Option Set which we want as legend entries and City is the parameter.

Solution: While creating the Chart, On Category[X axis], I had put first “City” then “Attending?”, & on the Y axis choose any other unique(not null) column, in this case I have done a “Count All” on “Employee ID” as shown below:

Lets see the result on Chart:

As you can see, the charts are coming so beautifully with Option Set values as Legend entries.

Point to Note :- Place the parameter value first and then the Option Set value on X-axis as category, not the opposite way like below, sequence matters:

This will result in something like this which we do not want:

This was simple yet a good way of showing option set entries on charts as legends instead of creating different views which equals each option set values

Hope it helps!

& the Power Affirmation for the day is:
“I am bigger than my doubts, bolder than my fears, and stronger than my obstacles.”

Handling Special Characters in Power Automate while using Fetch XML

Hi Amazing people!
Today i have got one of the recent requirements i came through.

Business Requirement: We had a string coming from a source which had to be compared with the account name and then further actions had to be followed. But certain accounts had special characters in it like “&” in “Larsen & Toubro” and other special characters.

Problem Faced: While writing the Fetch XML of comparison in “List Rows” action, if such special characters are present in the Account name, it throws an “Invalid XML” error.

Handling them on a case by case basis would be a nightmare plus its not the correct solution. So i digged into the functions available in Power Automate and found a life saviour – EncodeURIComponent()

Discussion: This function encodes a URI by replacing each instance of certain characters by one, two, three, or four escape sequences representing the UTF-8  encoding of the character.

It is widely used to return a string that replaces URL-unsafe characters with escape characters but can be utilised in such cases too. Let us see how!

Solution: Encode the string to be compared before sending it to fetch or within the Fetch itself.

PS: the Account name! Well i am awesome 😉

It will convert the special characters with escape characters like below:

Best part, it works for most of the special characters in use. Voila! Your flow runs successfully now:

Hope it helps!
& the Power Affirmation of the day is:
“I am proud of myself and my achievements”

Steps to Customize Opportunity Close Forms in D365 Sales

Hello everyone!
In this blog, we will learn how to customize the “Opportunity Close form” in Dynamics 365.

Business Requirement: Many times our clients have requirements where they want to capture essential details about why an opportunity was closed. By enabling this customization, sales representatives can provide valuable insights, helping sales managers develop better strategies.

Solution: So usually, when the users go through the normal opportunity process closure process, they will get only limited features while they choose “Close as Won” or “Close as Lost” on the Ribbon.

where they will get only these 5 fields to capture details.

But sellers can record relevant information such as the reason for closing an opportunity, profit margins,etc. These details help sales managers gain key insights and develop better strategies. Also, it allows an opportunity for more comprehensive reporting and analysis.

Implementation:

• Login into your Power Apps.
• Open “Sales Hub” App.
• On the left bottom corner, click on “Sales”.
• And change area to “App Settings”.

• Click on “Lead+opportunity management”.
• Switch to “Opportunity Closing”.
• Turn radio button to yes and hit the save button.

• Now edit your “Opportunity Close” form.
• Add desired custom field, for eg; in my form I have added “Profit Amount”.
• Save and publish your form.

Lets see it in action!
Go back to the Power App and click on “Close as Won” or “Close as Lost”,you will see that the dialog box won’t pop-up this time. Instead, the form will load as a “Quick create form”.

Voila! Now you can store as much information as you want with multiple details to analyze.
Hope it helps..

& the Power Affirmation for the month is :
“I am Strong and I will continue to be the same.”

How to automatically get Month Name & Number from a Date field in D365

Business Scenario: Well we have all used Excel to get columns of Month name from a Date and then we can count and have a pivot around it. If we have a similar such requirement in Dynamics 365, where we have the date value, how to get its Month name automatically in a column?
What comes to your mind? Calculated column would be the first thought right, try it out, i did, but no luck! This made me think that Microsoft must have definitely come up with a solution for this as this is an everyday scenario. I researched on it and landed on the much powerful Fx formula datatype

Solution: So i have a joining date column and when the record is created(not necessarily as data input on the form but by import or other ways), I want the Month Number & Month Name automatically populated so that later i can use it for reporting purpose, how many people joined in respective months. So how do I do it?

Step 1: Firstly your Date column should be “Date Only” and the Time Zone Adjustment should be made “Time Zone Independent”

Step 2: Make the Month Number column as “fx formula” type

and write in the formulae

Notice that Month() just gives you the number and it has automatically taken the datatype as Decimal fx. Thus it will give you the value 11.00. To make it just 11. Go to advanced find and make the decimal places as 0.

Now how to get the month name !? there is no nameOfMonth() function !? Well..

Step 3: Make the Month Name column as “fx formula” type and write in a simple switch statement for it.

& you are done.. Hope it helps!

This brings to another interesting use case we use around pivots in excel, what if now i wanted to count the number of joinees in each month? How can i add a column of Headcount which will store “1” in general and i can sum up all such 1’s according to the months and get the result? Think on it – will you now use calculated column or fx formulae.

& the Power Affirmation for this month in particular and this year in general is: ” I will be consistent!”

How to call Child Flows & find the respective Parent Flow from it!?

So, we have earlier learnt about the workflow() function in Power Automate. Today, let me tell you one of the other use case of it.

Business Requirement: After a while, there would be many runs of both the Parent & the Child flows and at some point it will become very difficult to connect the two related flow runs in case if the child flow runs are failing and you wanted to debug it. It will be extremely difficult to locate the respective parent flow run from the child flow run.

Solution: Thus in this blog, we will learn firstly how to create a child flow from parent flow & then how to quickly find the parent from the child without any hustle.
Let’s Start!

Use: Why do we use Child Flow anyway ? It can perform a specific function or task that is reusable across different parent flows. This is a useful technique if you want to reuse some logic or functionality in multiple flows, or if you want to break down a complex flow into smaller parts.

Let’s create the child flow first:

Step 1: Trigger

Step 2: Compose the email body

Step 2: Send and Email to the Contact

Now the Parent Flow: We are taking the scenario where on create of a contact, we are sending an email to the contact for successful creation.
Let’s create it:

Step 1: Trigger

Step 2: Add variables for “Contact name (String)”, “Email Address(String).

Step 3: Use the action as “Run a child flow” to trigger another flow by selecting it and pass data between them.

Save and test the Flow.

Parent Run:

Child Run:

Both the flows ran successfully!

Tip: To trigger a child flow, both the flows need to be in the same solution. If your flows aren’t solution aware, you will run into issues when trying to implement child flows. When you export the solution that contains these two flows and import it into another environment, the new parent and child flows are automatically linked, so there’s no need to update URLs.

Also if your parent flow is triggered from a Canvas app. Then you could use dynamic content that your app passes into your parent flow, and then further pass that into your child flow for information such as the user who clicked the button or the user that was running the app at the given time

Now, I can do further error handling here to do a condition where my parent flow will continue if my child flow was successful, but it will terminate if my child flow wasn’t successful.
To return values back to the parent flow, the last action should be a Respond to PowerApp of Flow action.

Life Saver Tip: Now, if you come after a while and see some of your child flows failing and want to debug it but have to search the parent run from the long run history of the parent flow, it is a cumbersome process. To make things easier, I have an easy way out for you.

Step 1: Pass the Run URL of the parent flow as a parameter to the child flow.

Step 2: This way you can make it as a column on the Run History table by clicking on the “Edit Columns” and selecting your parameter.

Voila! Here is the final result: Now you can copy the run url of parent flow directly from here.
Though i sincerely hope Microsoft makes it in the URl format someday and not a text one, so we will be able to directly land on the parent flow run page. Hoping!

Thus now that you know how to create a child flow and refer back the parent flow, Why don’t you start using it.
Flows with a large number of actions may encounter performance issues while you edit them, even if they have fewer than 500. Consider using child flows to reduce the number of actions in a single flow or if you need more than 500.
See you next week!

& the Power Affirmation of the day is:
“I trust that I’m heading in the right direction.”

Create your own Power Automate History table records-Part 2-How to save the records using Elastic tables?

Well! Let’s continue to create our very own custom Power Automate history table. In our last Blog, we got to know how to get the Run URL. Today, we will use it to create the records. You may create it using a standard table but we are going to use Elastic tables.So what are Elastic tables we are all hearing? Lets dig in!!

Theory: An elastic table is a table managed by Microsoft Dataverse which has similar user experience and API that are offered with standard tables. They share many aspects and options which comes with standard tables, but have their own unique features and capabilities that are powered by Azure Cosmos DB.
PS: This is a preview feature and not meant for production use and may have restricted functionality which may change in future.
Now when you create a table, you have 4 types of options.

As soon as you create one, you see 2 new columns which are not meant for standard tables.

Partition ID: It is a single line of text and is a system-defined Partition Id string column.

Time to Live: It is a whole number and stores data in no. of seconds.This capability allows removal of data automatically after fixed periods and ensure optimization of storage capacity.

Explanation: Let me make it simple for you to comprehend, lets say you have a table that contains data about different books, you can use the book category as the partition id value for the table. In this case, groups of books that have specific values for the book category like Fiction, Non-Fiction, Biography, Historical, etc form distinct logical partitions. hope you got some of the logic, for Developer enthusiasts, you may visit the MS documentation.

Now create the fields you would like to store information in:
Run URL – Stores the hyperlink to the Run. [ Make sure to increase the character from 100 to 250.]
Value : if you want to track the changing values.

Business Requirement: You want to create your own Power Automate history table, lets say for n no. of days and clear it off post that. Also you may do it for all your flows, or for some flows you want to specially track. You might also store the changed values too in the same record just to search easily.

Solution: Lets start!
Step 1: Initialize a variable which stores the number of days you want that record to stay in the system. You may take it as input too. Also, if you do not want a time limitation, leave the Time-to-live field blank and it will stay in the system forever until any further deletion.

Step 2: Create a new record in your Power Automate History table.

For the partition id, i have used the formulae, workflow().tags.flowDisplayName which stores the flow name which runs. Thus i have used the flow names to partition the table from one to another. You may also use the Flow Guid as the names can change.
Created On basically will store when that flow ran. You can store it explicitly using utcNow() in a separate field.
For the Run URL, using the expression we learnt last week.
For Time to Live,i have used mul(variables(‘Days’),86400) expression. A day has 86400 seconds and we can multiply it with the number of days to keep it existing in the system for the number of days you specify.

& the result is below:

So now you may easily track, sort & straightaway jump into the flow runs to debug it.

Also a great advantage if using it is, if you try to update the records manually from the UI post creation, you will not be able to do it which i feel is good in a way as you do not want to tamper with the Audit History.

Here is one more idea, you may introduce the status column in this table and mark it Success,failed or Cancelled depending on your Power Automate flow run logic as the last step using branching.
Happy Flowing! Happy Automation! Happy Brainstorming!

& the Power Affirmation for the day is:
“Be elastic by nature but always come back to your roots”