D365 Finance | Filter for Blanks

Use “” and the “is exactly” filter. This video could have been a blogpost. This blogpost could have been a tweet.

That said, this video contains a few extra nuggets that may be of use – how to create a listpage view that filters for blanks. How to use Power Query to report on records with blank values.

Hope you enjoy.

Some Concepts:

  • Filter for blanks (use “” and “is exactly” filter)
  • Add field to listpage
  • Create view from filtered listpage
  • Open in Excel
  • Use Power Query to create filtered view of data
  • Use Power Query to track changes to source data
Continue reading

D365 Finance | Connect Directly to D365 Data with OData and Power Query

Welcome to the matrix!

In this video, we look at how D365 data entities work with OData, how to find the entities available to you through OData, and how to connect to those entities using Power Query in Excel.

Some Concepts:

  • OData
  • OData in D365
  • Connecting to OData in Excel through Power Query
  • Data entity names are case sensitive and likely need dataareaid (legal enitity) defined.

Some References:

Continue reading

D365 Finance | Supercharge Excel Add-in with Power Query

In this video, we look at a simple example of how Power Query can supercharge your reporting abilities for values brought into excel via the data connector.

I’ve found Power Query to be a great tool for efficiently combining data that lives across two different D365 data entities. It is a fantastic tool for data migration or analysis in general.

Some Concepts:

  • Pivot Tables
  • Refreshing Data
  • Connecting Data to Power Query
  • Merging Two Data Sources in Power Query
  • D365 Relationship Between Projects, Project Contracts, and Sales Currency
Continue reading

D365 Finance | Excel Add-in Reports with Formulas

In this video, we look at using formulas within the excel data connector.

As an excel user, you should be familiar with formulas. Perhaps you frequently take D365 data into excel to perform some external lookups or comparisons. Formulas can be written into your D365 excel-connected experience, so that your dependent formulas refresh when you refresh your connection.

Some Concepts:

  • Using Excel Data Connector Add-in for D365
  • Adding Formulas to Connected Output
  • D365 list-page grouping and totaling
  • CountIf() and VLookup() formulas in Excel
Continue reading

D365 Finance | Excel Add-in Data Connector from Scratch

In this video, we look at building a master data report using the excel data connector.

Building the report from scratch instead of relying on the “open in excel” template experiences helped me better understand the connections made using the tool.

Some Concepts:

  • Using Excel Data Connector Add-in
  • Why Does Edge Open My Excel Download File In a New Tab First?
  • Getting Dynamics Connector Add-in for Excel
  • Connecting to Dynamics environment through Dynamics Connector
  • Creating Report Across Multiple Data Entities in One Excel Table
Continue reading

Project Financial Dimension Report – D365 Finance & Excel

In this video, we look at building a report based on project financial dimensions using the excel data connector.

Some Concepts:

  • Master data reporting through listpage views
  • Export to excel vs Open in Excel
  • Financial dimension configuration for OData connections
  • Mediocre usage of text-to-columns in Excel
  • Financial dimension and chart of accounts delimiter
Continue reading

Data Violates Integrity Constraints – Data Management in D365

Failure is no fun.

“Data Violates Integrity Constraints” is an error I encounter too often. If you get this error, perhaps this post will help you push past your failure.

In the scenario below, you try to import excel data through a data entity, but get a failure error.

That’s fine you say, I’m a professional. So you click view execution log to troubleshoot the issue.

If you try to view the staging data, you’ll see no staging data has loaded.
If you click on Staging log details, you’ll get a curious error message:
The data value violates integrity constraints.

This is curious, because it seems you haven’t loaded any data at all, let alone some data that would have triggered a validation error. You re-check your excel file, and the data you wanted to load seems correct.

So What’s The Problem? How do you fix it?

Frequently, the issue is there are some “blank” rows at the bottom of your excel data that are throwing off the upload. Data Management is trying to prevent you from importing those blank rows. Highlight the row below your data, press CTRL+SHIFT+DOWN and delete all the rows below your data. To be safe, you can do the same thing to the far right to clean up any columns that may be causing trouble as well.

Then, just like wherever else we encounter failure, set it up and try, try again.

Good luck!

Power Query Demo – Connecting D365 Tables in Excel

A D365 Finance and Operations reporting issue I had recently:
How do I report on all published WBS activities by their related projects?

Well, the activities table (smmactivities) doesn’t have a project id field.
If you are here for that specific question, the necessary table relationships are:

  • smmActivities
  • HierarchyTreeTable
  • HierarchyLinkTable
  • ProjTable

But I was interested in the process of doing this more than the specific reporting task. A basic way to do this could be to dump those tables into excel, then use a bunch of vlookups to add the associated project data to the activity rows.

Nothing wrong with that, but the final product would be a bit clunky…the whole smmActivities table, with a few extra columns. You could then delete or hide the unnecessary columns, but if you need to create the report again you’ll have to redo those same operations. If you need to add some attribute, you’ll have to do more vlookups (or xlookups, or index matches, whatever your preferred Excel operation). Is there a better way?

Introducing Power Query

Power Query is a tool to get and transform data in excel. In the example below, I exported the 4 desired tables, pointed to them as data sources, joined them together in Power Query, and then used Power Query to clean up my reporting output.

Here’s a video of that effort:

Reasons to Use Power Query

A Better Way to Get Data
Power Query can connect to other tables in your excel file (like in my example above). Power Query can also be used to connect to other external data sources. In a more sophisticated reporting example, there is probably a way I could connect directly to a D365 data entity. I’ll look into that more in the future.

A Better Way to Transform Data
I found Power Query to be a really pleasant experience for transforming data. A frequent D365 data migration issue is you’re given some legacy file, with some legacy file headers, which needs to be formatted into a D365 structure of columns and headers.

Instead of hacking away at the source data, which would be my first excel instinct. Power Query lets you add columns or rename headers programmatically…making it simple to redo the changes if you later get a NEW file of legacy data that also needs to be transformed.

I particularly like that Power Query shows a list of “applied steps”, which makes it easy to follow the transformations that take place between the original and transformed data.

Power Query shows applied steps in the process of transforming data

My first-time user experience is that Power Query is a great tool, and I can see it becoming an integral part of improving D365 data migration projects.

Additional Resources