Splitter.SplitByNothing - Splitter Function

Splitter.SplitByNothing is a Power Query M function that returns a function that does no splitting. The function returns its argument as a single element list.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Splitter.SplitByNothing() as function

Description

The core purpose of the Splitter.SplitByNothing function is to abstain from performing any splitting. The function delivers its output in the form of a single element list.

Examples

To get a better grasp, let’s explore how the Splitter.SplitByNothing function behaves.

Basic Splitting Operation

The Splitter.SplitByNothing function returns a function that does no splitting. When you provide it with a value, it returns the result in the form of a list.

Splitter.SplitByNothing() ( 5 )       // Output: { 5 }
Splitter.SplitByNothing() ( "a" )     // Output: { "a" }
Splitter.SplitByNothing() ( {1,2,3} ) // Output: {{1, 2, 3}}

Power Query’s Splitter.SplitByNothing function might seem less useful initially, as it essentially performs no splitting, returning its argument as a single element list. But there are practical scenarios where this function can prove incredibly beneficial.

Overriding the default behavior of Table.FromList

Imagine a scenario where we’re using the Table.FromList function on strings separated with commas. By default, Power Query takes commas as separators for distinct columns. For instance, if we run:

Table.FromList( {"1,2,3","4,5,6"} )

Power Query returns a table with 3 columns and 2 rows because it splits the string by the commas. But what if we want to prevent this splitting behavior?

Here comes the role of Splitter.SplitByNothing. It can override this default behavior, ensuring that each string is treated as a single element.

Table.FromList( {"1,2,3","4,5,6"}, Splitter.SplitByNothing() )

Using Table.FromList with Splitter.SplitByNothing

Executing this command results in a table with only one column and two rows. The commas within the strings are ignored.

Handling Numeric Lists in Table.FromList

Another scenario where we can use Table.FromList is when using a list of numeric data. Take this example:

Table.FromList( {1,2,3} )

Table.FromList throwing an Expression.Error for a list of numbers

Power Query throws an error because the Table.FromList function assumes the list to contain text values split by commas.To prevent this behavior the Splitter.SplitByNothing function can help.

Table.FromList( {1,2,3}, Splitter.SplitByNothing() )

Table.FromList with list of numbers and Splitter.SplitByNothing

This ensures that each number in the list is treated as an individual element, avoiding any errors.

These examples illustrate that Splitter.SplitByNothing can indeed be a useful tool in handling specific Power Query scenarios. By knowing when to use it, we can get more control over our data transformations.

Used by

While you can use the Splitter.SplitByNothing function by itself, it also works together with:

Other functions related to Splitter.SplitByNothing are:

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/splitter-splitbynothing