Text.PadStart is a Power Query (M) function that works by inserting characters or spaces at the beginning of a record until it reaches a specified length. The function returns the padded text value.
This function is very useful for adjusting invalid foreign keys that arise out of missing characters like trailing zeros. This article contains a brief explanation of the function and an example for fixing invalid keys with Text.PadStart.
Syntax
Text.PadStart(
input as nullable text,
count as number,
optional character as nullable text,
) as nullable text
The Text.PadStart function Returns a [text] that is padded to a length [count] by inserting spaces at the start of the [input]. An [optional character] is used to specify the character used for padding. If no optional character is specified, the default pad character is a space.
The input field must be a text type, else the function returns an error.
This can fixed by modifying the input as text with the function Text.From(), or by defining the column’s data type as a text type in a previous step.
For more information about the syntaxis, check out this Microsoft post.
Example
To better understand when to use the Text.StartPad function an example will be provided.
Below are two tables that belong to company A. The table on the left, the Dimension Table, contains information about the products sold. The table on right has the sales per month by product, this is the Fact Table.
When the records for April were manually pasted into Excel the product IDs were mistaken as numbers, so the trailing zeros were removed. This error can be observed on the cells in yellow.
For Power Pivot to make a link between the foreign key to the primary key, the keys must match with the primary keys in the Dimension Table.
Notice how in the pivot table below there is a blank row for the records aggregated with the incorrect IDs. When Power Pivot cannot find a link between the primary key and foreign key, it returns all the unlinked records under an aggregated default blank row.
To solve this the Text.PadStart function will be used to pad the missing trailing zeros on the column “ProductID” on the fact table. A step was added with the following function:
Text.PadStart = Table.TransformColumns(Source,{{"ProductID",each Text.PadStart(Text.From(_),7,"0"), type text}})
For the function to work properly, it must specify the correct number of zeros “0”. The function pads the Product ID with three “0” at the start, making the strings 7 characters long.
As you can see, the Text.PadStart function is not only easy to use but also very useful. It lets you handle a wide range of text formatting tasks, and can be used to modify missing keys. When refreshing the data model, the blank row is no longer present and the keys match correctly.
If you’d like to learn more about Power BI, check my other posts on this link.