In my last article, Creating a Summarize List by States Between Two Systems, I talked about using a calculated field to do a data transformation that converted the state field from the full name to their respective abbreviation. Maybe calculated fields are not your thing, so let’s look at a different way to accomplish this. This approach has an added benefit that can provide extra information that would be much harder to do with a calculated field.

Problem

The problem is the same as before, the Sample data list is using the State Names and the Dynamics GP (Sample Data) list is using the State Abbreviations and I need a way to get the two different systems to use one or the other, either the State Names or the State Abbreviations. Let me get a couple of tools out of the Popdock toolbox: Azure Data Lake files and Join lists.

Solution

Like before, I need to do a data transformation on the State data from one connector to match the State data in the other connector. But instead of using a calculated field, I am going to store all the data in a .csv file and put it into an Azure Data Lake blob. The .csv file needs to have two columns, State and Abbreviations, or ‘Abbr’ for a shorter column name.

After you create and save the .csv file, you need to upload the file to an Azure Data Lake blob. But you might have noticed that there are more than the two required columns here. This is where we can add more ability to our Summary list, but more on that later.

Then, in Popdock we need to add the .csv file to your ADL connector.

Go to: Connectors >> edit Azure Data Lake >> Lists. Then select Add a file from Azure Data Lake.

Now we can go to the Sample data connector to create a join list between the Customer list and the new US_States.csv list.

Go to: Connectors >> edit Sample data >> Lists. Then select Add custom list. Select Joined.

  1. Enter Customers-States-Abbr for the Name.
  2. Add Lists and select the fields you want. From the Sample data, select the Customer list. From the Azure Data Lake, select the US_State.csv.
    • Left outer join on fields from Customer/State to US_States.csv/State.
  3. The following fields for each list. It should look like this:

Conclusion

At this point, we now have both Customer lists returning State Abbreviations. Which gets us back to the Conclusion section in the original article, Creating a Summarize List by States Between Two Systems, where you can create a merge list between the two customer lists which can be used to finish the last part of creating a summary list by states. The only difference is that with this approach we created a merge list that used the join list between the customer list and the data transformation list in Azure Data Lake.

Hopefully this was helpful and sparked a thought about how you could use Azure Data Lake files to make a data transformation between systems more effectively.

BONUS

I mentioned at the beginning that we can provide additional abilities using a custom join list. In the data transformation list, you can add additional columns that go with the main data. In this example, I added a ‘Region’ column for each state. There are four regions: Northeast, South, Midwest and West. This allows us to create a summary list by region.

To do this, first we need to create a join list with the Dynamics GP (SampleData) \ Customers list and the data transformation list in Azure Data Lake to associate the regions. Follow the same steps as shown above.

Next, create a merge list using both join lists.

Finally, create a summary list based on the merge list to get a Summary list by regions.

DOUBLE BONUS

If you want to create another Summary report that includes both region and state, you can use it to view the states per region in as a detail.