Power Platform Tidbits 10: How to split address into separate columns in Power Query

Sometimes address values are stored in a single text column and you’d need to split it into separate columns. For example when migrating data into Power Platform in a data flow, or when building a Power BI dataflow, or simply in Excel. In this post am going to show you how you can do this in a reliable way. I am going to make some assumptions first, but you can always fine-tune the formula for other address patterns.


The addresses I’m going to work with look like the followings.

  • François Vervloetstraat 100, 1080 Uccle
  • Koning Albertstraat 14, Menen

The syntax we are looking for is:

first-line, [postcode] city-name

In other words, the first line address is always present. The postcode is optional. the city name is always present. It might be that there is no address at all.


  #"Split address" = Table.SplitColumn(#"Previous step", "address", Splitter.SplitTextByDelimiter(","), {"Line 1", "Line 2"}),
  #"Split by character transition" = Table.SplitColumn(#"Split address", "Line 2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Postcode", "City"}),
  #"Replaced nulls" = Table.ReplaceValue(#"Split by character transition", null, each [Postcode], Replacer.ReplaceValue, {"City"}),
  #"Replaced postcodes" = Table.ReplaceValue(#"Replaced nulls", each [Postcode], each if [Postcode] = [City] then null else [Postcode] , Replacer.ReplaceValue, {"Postcode"}),
  #"Changed types" = Table.TransformColumnTypes(#"Replaced nulls", {{"Line 1", type text}, {"Postcode", type Int16.Type}, {"City", type text}})

Let’s go over the solution, line by line.

  1. The first line assumes that there is a Previous step and an address column. It splits the address column into two columns, Line 1 and Line 2 by looking for a comma character.
  2. The Line 2 column is split into two other columns Postcode and City based on character transition from digits ({"0".."9"}) to non-digits. If the postcode was not optional, we could stop here, but we need two more steps to address this issue.
  3. This step sets the Postcode to the City whenever Postcode is null. After this step, whenever the postcode is not present in the address, both Postcode and City column will contain the city name.
  4. This step sets the Postcode to null whenever the Postcode and City columns have the same value as the result of previous step.
  5. The last step is just setting the type of columns, because after applying Table.SplitColumn transition, the new columns might not have proper types.

More about splitting by character transition

The most interesting step of the solution is the one that splits columns based on character transition. Let’s break it down. This step is a mix of two different table functions. If we look at the syntax of Table.SplitColumn function, it looks like this:

Table.SplitColumn(table as table, sourceColumn as text, splitter as function, optional columnNamesOrNumber as any, optional default as any, optional extraColumns as any) as table

The first parameter is the table, the second one is the name of the column that will split, the third one is the function that splits the value. This function will be given the value of the column and should in turn, return a list of values. It could be as simple as (value) => {"value 1", "value"}, but we use a native function that can split values for us, and that is Splitter.SplitTextByCharacterTransition. This function has the following syntax:

Splitter.SplitTextByCharacterTransition(before as anynonnull, after as anynonnull) as function

This function splits text into a list of texts according to a transition from one kind of character to another. The before and after parameters can either be a list of characters, or a function that takes a character and returns true/false. As before we are passing {"0".."9"}, but for after we are passing a function to detect any characters other than {"0".."9"} this makes the detection more inclusive. In other words (c) => not List.Contains({"0".."9"}, c).

Read more

Azure Uploader for Power Platform is here

Azure Storage with its high availability and numerous features has always been a storage of choice for many requirements. When it comes to Power Platform we should carefully weigh the options that we have for storing files. Besides the native Dataverse that is fully integrated with the platform, but comes with the highest costs, there are other alternatives like SharePoint and Blob storage in Azure Storage.

Diagram displays a horizontal line with three logos displayed on top. On the left, the line points to Best integration,
Power Platform storage options comparison

Azure Uploader is a PCF control that is built to reduce the effort of integrating with Azure’s Blob storage. It makes it easy to upload files with virtually no limit in size, directly to blob storage in a secure way.

You might be wondering why directly from the browser and what about the security. To store files in blob container you have two major options.

  • The first option is to upload the file to a proxy service and the proxy service would then upload the file to the storage behind the scene. This gives you the advantage of being able to do some processing (e.g. validate the content) before storing the file. But it comes with the extra effort required to build the service and maintain it over time. In addition to that you need to make sure this new service has the required availability (or SLA) required for your application. As the old saying goes, you will have one more layer that things can go wrong.
  • The second option is to have a very simple proxy service (that you might already have) generate a short-lived SAS token with limited access, share it with the client and the client would take care of uploading the file. This way you can rely on the availability and capacity of Azure Storage service.

You should consider that files coming from the client cannot be trusted, so you might need to put in place a process to validate them before use. In the first approach the proxy service is able to validate files during the transfer, but in the second approach you should do this validation later. For example a Power Automate flow, a Logic App, or an Azure Function App can be triggered and do this asynchronously.

Azure Uploader relies on the second approach. This means that you just need to generate a SAS token in your app and share it with the uploader. Generating a SAS token is very easy thanks to the out-of-the-box Azure Storage Blob connector.

The following Power FX code, generates a new SAS token using Azure Storage Blob connector (called AzureBlobStorage) and stores it in a variable called SasToken.

            AccessProtocol: "HttpsOnly",
            ExpiryTime: DateAdd(Now(), Hours, 1),
    Mid(SasResult.WebUrl, Find("?", SasResult.WebUrl) + 1)
  • The above code, generates a code that is valid only for one hour DateAdd(Now(), Hours, 1) and only has Write,Add,Create permissions.
  • The SasResult variable that is set in the first step contains a property WebUrl which holds a URL that has the SAS token in its query string. That is why the line Mid(SasResult.WebUrl, Find("?", SasResult.WebUrl) +1) is used to extract only the part after “?” store it in SasToken variable.

Power Platform Tidbits 9: Truncate a table in Dataverse with one command

The latest version of PSDataverse (v0.0.3) brings several quality and performance improvements. But, it also has a new command that lets you delete all the rows in a table in Dataverse as fast as it can be done. Here is the easiest way to use it:

Clear-DataverseTable lead

If you need to truncate multiple tables in one go, you can just pass multiple table names separated by comma.

Clear-DataverseTable lead, opportunity

You can also pipe the list of table names to Clear-DataverseTable. This can be useful for when you are receiving the list from some other command.

@(lead, opportunity) | Clear-DataverseTable

Note that you should be already connected to Dataverse using ‘Connect-Dataverse’ before running the ‘Clear-DataverseTable`. This command is part of PSDataverse module that you can get from PowerShell Gallery. To know more, I suggest you read about it in here.

Good to know

When passing in a list of values separated by comma, PowerShell assumes that you are sending an array. The full syntax for literally defining an array is @(item1, item2,…). Knowing that, if you pass an array however way to the Clear-DataverseTable it would work. To know more about arrays in PowerShell and all the different ways to define them, check About Arrays in the official documentation.

Power Platform Tidbits 8: Download the full metadata of your Dataverse environment by one PowerShell command

You can always visit get the full metadata of a Power Platform environment (or Dynamics 365) from https://{your-environment}.{region-specific-url}/api/data/v9.2/$metadata. For example if your environment is hosted in Europe, the URL can be:


But sometimes the data can be too much for your browser to digest, specially if you have a plugin that formats or beautifies XML content. In that case you can always use PowerShell or more precisely PSDataverse to download and save the metadata as an XML file. It just takes two lines in Powershell, and the first line is to connect.

Connect-Dataverse "authority=https://login.microsoftonline.com/{tenant-id}/oauth2/authorize;clientid=1950a258-227b-4e31-a9cf-717495945fc2;resource=https://mytestenvironment.crm4.dynamics.com/;device=true"
Send-DataverseOperation "`$metadata" | Select-Object -ExpandProperty Content | Set-Content ".\schema.xml"

A screenshot of Windows Terminal. The screenshot shows two commands and their result. The first command is "Connect-Dataverse" with a connection string that uses device flow to authenticate. The second command is "Send-DataverseOperation "`$metadata" | Select-Object -ExpandProperty Content | Set-Content -Path ".\schema.xml".
Both commands are executed successfully.
In my case, it took 8 seconds to download and save the file from a trial environment.

If you are not familiar with PSDataverse, just know that it takes only one line to install it. Read more in the official Github repository

How to import large CSV files in Power Platform (or Dynamics 365)

There are several ways you can import data into Power Platform (or Dynamics 365). When you are importing CSV files, you have the following out-of-the-box options, each with their cons and pros.

  • Import feature in Power Apps – Allows you to import Excel (.xlsx), comma-separated values (.csv), and XML Shpreadsheet 2003 (.xml) formats. The data can be compressed, but the maximum size is only 32 MB for the .zip files.
    • PRO: Easy-to-use UI that guids you through the process.
    • CON: Limited files size.
    • CON: No transformation / advanced mapping logic allowed.
    • CON: Very unforgiving in the format of data
    • CON: No so fast.
    • CON: Cannot be automated
  • Power Platform Data flows – can be used for both data migration and continues data integration. Data flows is a new and exciting way of importing data to Power Platform based on Power Query. It supports tons of different data formats that includes even external Web APIs, Parquet files, and even another Dataverse!
    • PRO: Based on Power Query – I consider it a pro, because you can learn Power Query in minutes and the UI is unparalleled to any tool that I have seen in my career.
    • PRO: Can handle large files (although I haven’t tested at gigabyte level yet).
    • PRO: Advanced data transformations can be applied easily on the fly
    • PRO: Support a wide range of data sources, although we are concerned about CSV here, but you never know maybe that CSV comes from another source that is supported 😉.
    • CON: Absolutely no way to manage failures in the data flow. You will have access to a report at the end that helps you track down which records failed, but if you have serveral failures it will not be practival to read the report and filter those manually!
    • CON: Performance is usually better compared to the import feature, but very unreliable. I used it in project where the scope of tables were limited and eveyy time I tested I got totally different performance.
    • CON/PRO: Can be automated, but only based on a schedule.
  • Custom cloud flow (or Power App) – Sky is the limit when it comes to app development right? but, what about the effort? What if you need to do it in different environments, are you goin to also implement CI/CD? How reusable your flow / app will be?
  • Using Azure Synapse Analytics – is as the name suggests, an analytics service on Azure. While it is not a free service like the previous options that I iterated, but provisioning it for the sake of a full data migration project when the number of tables is more than a handful can be extremely benefitial. The fact that you can just focus on the transformation logic and Synapse would take care of managing the data storage will save you so much time. It supports so many data source and targets out-of-the-box and if you already have some background with a database technology (e.g. SQL Server) the learning curve won’t be steep.
  • SQL Server’s SSIS – is a wellknown ETL tool that has been used for data integration and data migration for ages.
    • PRO: Familiar tool (if you know your way around SQL Server family of products) and an easy-to-use UI.
    • PRO: Part of SQL Server
    • PRO: Several out-of-the-box and 3rd party components. In case of Power Platform I recommend Kingswaysoft’s components which I have used several time in many projects with success. They also have a great support team for those edge cases 😉
    • PRO: Reliable tool with good performance
    • CON: Implementing complex logic and debugging can be difficult.
    • CON: In many cases you will have to rely on ScriptComponent to run C# code pieces that will be hidden in multiple components that is not reusable.
    • CON: Although you would be producing code behind the scene, the storage format makes it impossible to trace code changes or revert changes.
    • CON: Since the introduction of Synapse, I find it really hard to recommend SSIS for any but a very narrow range of use cases (e.g. you already have it and use it for another similar ETL process).

As you see, there are several options, but all of them are somehow limited by either size, or performance or fragile and limited when it comes to handling fault, apart from Azure Synapse Analytics. The latter might not be justifiable in terms of cost or learning curve compared to the scope of the project. Today I’m going to show you another, very easy way to load large amounts of data in Power Platform that can be another tool in your toolbox. This one aims to address the shortcoming of the above options that you have.

Using PSDataverse to load large CSV files

Chances are you have landed here because you have a large CSV file and you might be asking how large are we talking. The answer is I have not faced any limit yet. When done right, virtually you should not face any limit. I have personally tested with larg CSV files of several gigabytes without any issues.

I have posted about PSDataverse before in this blog and in fact previously I showed an example where I load a CSV file into Accounts table. Let’s start from there. The only prerequisite is to install PSDataverse, a PowerShell module that has no other dependecy. You can either install it once from the official PowerShell gallery using the following command:

Install-Module -Name PSDataverse

Or, you can get it from its official Gihub repository and install it manually.

Connect-Dataverse "authority=https://login.microsoftonline.com/<tenant-id>/oauth2/authorize;clientid=<client-id>;clientsecret=<client-secret>;resource=https://<environment>.crm4.dynamics.com/"
Import-Csv "Account.csv" | ForEach-Object {
    @{ Uri = "account", Method= "PATCH", Value = @{
        name = $_.AccountName,
        overridencreatedon = $_.CreationDate,
        ...}}} | ConvertTo-Json | Send-DataverseOperation

The first line uses a client-id and a client-code to authenticates to Power Platform. There are different ways you can connect to Power Platform. Using a client-id and a client-secret, using a client-id and a client certificate or simply using a client-id and an automatically generated device code. In all the cases you will need to create an application registration and then create an application user from it. This can be done in literally a minute through the UI. Read more here if you haven’t done it before.

The second line does all the magic! It first imports a CSV file, then for each record in that CSV file does a simple transformation which is a basic mapping, and then converts it to a JSON model and sends it to Power Platform using Send-DataverseOperation command.

Tuning the performance

At this point, if you run the script, it will start pushing data to Power Platform one record at a time. It waits for the response and returns it to you in a simple PowerShell object. The problem with this approach is that while it is reliable and you will get back the response, the performance won’t be great. If you are a veteran Dynamics developer, you might already know two important features: parallelism and batching.

By parallelism, I mean PowerShell allows you to push data concurrently using up to 20 streams in parallel which can virtually increase the speed by almost 20x and this limit can be raised by Microsoft during your data migration by opening a support request.

Batching allows to send multiple operations in one batch, which means you will save on the amount of data transferred over the wire that can contribute to increasing performance. The hard limit is 1000 operations, but you should try different numbers, because depending on the table, its relations and the logic around it, the number of operations you put in a batch can worsen the performance as well. My suggestion is start with 500 and fine-tune per each table to get the best performance.

Let’s rewrite our script to benefit from parallelism and batching.

Connect-Dataverse "authority=https://login.microsoftonline.com/<tenant-id>/oauth2/authorize;clientid=<client-id>;clientsecret=<client-secret>;resource=https://<environment>.crm4.dynamics.com/"
Import-Csv "Account.csv" | ForEach-Object {
    @{ Uri = "account", Method= "PATCH", Value = @{
        name = $_.AccountName,
        overridencreatedon = $_.CreationDate,
        ...}}} | ConvertTo-Json | Send-DataverseOperation -MaxDop 20 -BatchSize 500

As you see just by adding two parameters the performance will be increased by an order of magnitude. I will not give you numbers to get you hops high or low, because every environment is different, but I can guarantee that you will be impressed.

Advanced scenarios

While the example I gave before is very basic, you can extend it to add complex data transformations or even get data from other sources or even multiple sources. The Send-DataverseOperation will try its best to succeed. For example in case of transient errors (e.g. brief network outage) it will wait and retry multiple times and even loger each time. There is a lot more to it that you can learn from the Github repository.

I also suggest that you add a proper fault-handling to isolate and categorize failed records, so you can analyze how many records failed and why (e.g. name is one character too long 😄) . That way you can mitigate the problems and retry only the records that failed.

For scenarios where you have multiple tables with different transformations, I recommend a staging database. That way you can have a multi-stage migration. First you import data into the staging DB and apply some cleanup. Then you will transform and load data into Power Platform. It might sound like a lot of work, but the time you will save is not even comarable with the effort. Perhaps in another blog post, I will show how you can do it in a matter of minutes.