A
- AccessControlEntry.ConditionToIdentities
- Access.Database
- ActiveDirectory.Domains
- AdobeAnalytics.Cubes
- AdoDotNet.DataSource
- AdoDotNet.Query
- AnalysisServices.Database
- AnalysisServices.Databases
- AzureStorage.BlobContents
- AzureStorage.Blobs
- AzureStorage.DataLake
- AzureStorage.DataLakeContents
- AzureStorage.Tables
B
- BinaryFormat.Binary
- BinaryFormat.Byte
- BinaryFormat.ByteOrder
- BinaryFormat.Choice
- BinaryFormat.Decimal
- BinaryFormat.Double
- BinaryFormat.Group
- BinaryFormat.Length
- BinaryFormat.List
- BinaryFormat.Null
- BinaryFormat.Record
- BinaryFormat.SignedInteger16
- BinaryFormat.SignedInteger32
- BinaryFormat.SignedInteger64
- BinaryFormat.Single
- BinaryFormat.Text
- BinaryFormat.Transform
- BinaryFormat.UnsignedInteger16
- BinaryFormat.UnsignedInteger32
- BinaryFormat.UnsignedInteger64
- BinaryFormat.7BitEncodedSignedInteger
- BinaryFormat.7BitEncodedUnsignedInteger
- Binary data
- Binary.ApproximateLength
- Binary.Buffer
- Binary.Combine
- Binary.Compress
- Binary.Decompress
- Binary.From
- Binary.FromList
- Binary.FromText
- Binary.InferContentType
- Binary.Length
- Binary.Range
- Binary.Split
- Binary.ToList
- Binary.ToText
- Binary.View
- Binary.ViewError
- Binary.ViewFunction
- Byte.From
C
- Cdm.Contents
- Character.FromNumber
- Character.ToNumber
- Combiner.CombineTextByDelimiter
- Combiner.CombineTextByEachDelimiter
- Combiner.CombineTextByLengths
- Combiner.CombineTextByPositions
- Combiner.CombineTextByRanges
- Comparer.FromCulture
- Comparer.Ordinal
- Comparer.OrdinalIgnoreCase
- Controlling byte order
- Csv.Document
- Cube.AddAndExpandDimensionColumn
- Cube.AddMeasureColumn
- Cube.ApplyParameter
- Cube.AttributeMemberId
- Cube.AttributeMemberProperty
- Cube.CollapseAndRemoveColumns
- Cube.Dimensions
- Cube.DisplayFolders
- Cube.MeasureProperties
- Cube.MeasureProperty
- Cube.Measures
- Cube.Parameters
- Cube.Properties
- Cube.PropertyKey
- Cube.ReplaceDimensions
- Cube.Transform
- Currency.From
D
- DateTime.AddZone
- DateTime.Date
- DateTime.FixedLocalNow
- DateTime.From
- DateTime.FromFileTime
- DateTime.FromText
- DateTime.IsInCurrentHour
- DateTime.IsInCurrentMinute
- DateTime.IsInCurrentSecond
- DateTime.IsInNextHour
- DateTime.IsInNextMinute
- DateTime.IsInNextNHours
- DateTime.IsInNextNMinutes
- DateTime.IsInNextNSeconds
- DateTime.IsInNextSecond
- DateTime.IsInPreviousHour
- DateTime.IsInPreviousMinute
- DateTime.IsInPreviousNHours
- DateTime.IsInPreviousNMinutes
- DateTime.IsInPreviousNSeconds
- DateTime.IsInPreviousSecond
- DateTime.LocalNow
- DateTime.Time
- DateTime.ToRecord
- DateTime.ToText
- Date.AddDays
- Date.AddMonths
- Date.AddQuarters
- Date.AddWeeks
- Date.AddYears
- Date.Day
- Date.DayOfWeek
- Date.DayOfWeekName
- Date.DayOfYear
- Date.DaysInMonth
- Date.EndOfDay
- Date.EndOfMonth
- Date.EndOfQuarter
- Date.EndOfWeek
- Date.EndOfYear
- Date.From
- Date.FromText
- Date.IsInCurrentDay
- Date.IsInCurrentMonth
- Date.IsInCurrentQuarter
- Date.IsInCurrentWeek
- Date.IsInCurrentYear
- Date.IsInNextDay
- Date.IsInNextMonth
- Date.IsInNextNDays
- Date.IsInNextNMonths
- Date.IsInNextNQuarters
- Date.IsInNextNWeeks
- Date.IsInNextNYears
- Date.IsInNextQuarter
- Date.IsInNextWeek
- Date.IsInNextYear
- Date.IsInPreviousDay
- Date.IsInPreviousMonth
- Date.IsInPreviousNDays
- Date.IsInPreviousNMonths
- Date.IsInPreviousNQuarters
- Date.IsInPreviousNWeeks
- Date.IsInPreviousNYears
- Date.IsInPreviousQuarter
- Date.IsInPreviousWeek
- Date.IsInPreviousYear
- Date.IsInYearToDate
- Date.IsLeapYear
- Date.Month
- Date.MonthName
- Date.QuarterOfYear
- Date.StartOfDay
- Date.StartOfMonth
- Date.StartOfQuarter
- Date.StartOfWeek
- Date.StartOfYear
- Date.ToRecord
- Date.ToText
- Date.WeekOfMonth
- Date.WeekOfYear
- Date.Year
- DB2.Database
- Decimal.From
- Diagnostics.ActivityId
- Diagnostics.Trace
- DirectQueryCapabilities.From
- Double.From
- Duration.Days
- Duration.From
- Duration.FromText
- Duration.Hours
- Duration.Minutes
- Duration.Seconds
- Duration.ToRecord
- Duration.TotalDays
- Duration.TotalHours
- Duration.TotalMinutes
- Duration.TotalSeconds
- Duration.ToText
E
F
G
H
I
L
- Lines.FromBinary
- Lines.FromText
- Lines.ToBinary
- Lines.ToText
- List.Accumulate
- List.AllTrue
- List.Alternate
- List.AnyTrue
- List.Average
- List.Buffer
- List.Combine
- List.ConformToPageReader
- List.Contains
- List.ContainsAll
- List.ContainsAny
- List.Count
- List.Covariance
- List.Dates
- List.DateTimes
- List.DateTimeZones
- List.Difference
- List.Distinct
- List.Durations
- List.FindText
- List.First
- List.FirstN
- List.Generate
- List.InsertRange
- List.Intersect
- List.IsDistinct
- List.IsEmpty
- List.Last
- List.LastN
- List.MatchesAll
- List.MatchesAny
- List.Max
- List.MaxN
- List.Median
- List.Min
- List.MinN
- List.Mode
- List.Modes
- List.NonNullCount
- List.Numbers
- List.Percentile
- List.PositionOf
- List.PositionOfAny
- List.Positions
- List.Product
- List.Random
- List.Range
- List.RemoveFirstN
- List.RemoveItems
- List.RemoveLastN
- List.RemoveMatchingItems
- List.RemoveNulls
- List.RemoveRange
- List.Repeat
- List.ReplaceMatchingItems
- List.ReplaceRange
- List.ReplaceValue
- List.Reverse
- List.Select
- List.Single
- List.SingleOrDefault
- List.Skip
- List.Sort
- List.Split
- List.StandardDeviation
- List.Sum
- List.Times
- List.Transform
- List.TransformMany
- List.Union
- List.Zip
- Logical.From
- Logical.FromText
In this article, we will delve into the M code behind this powerful function, explore its capabilities, and provide some practical examples of how it can be used to manipulate data.
Overview of the Combiner.CombineTextByEachDelimiter Function
The Combiner.CombineTextByEachDelimiter function is a Power Query function that allows you to combine text values in a column, separated by one or more delimiters of your choice. This function is particularly useful when you have data that is not formatted in a way that makes it easy to analyze.
The syntax for the Combiner.CombineTextByEachDelimiter function is as follows:
Combiner.CombineTextByEachDelimiter(delimiters as list, optional quoteStyle as nullable number) as function
The `delimiters` parameter is a list of one or more characters that you want to use as delimiters to separate the text values in the column. The `quoteStyle` parameter is optional and allows you to specify the type of quotes to use when combining the text values.
Here is an example of how you can use the Combiner.CombineTextByEachDelimiter function to combine text values in a column separated by a comma:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])),
CombinedText = Table.AddColumn(Source, “Combined Text”, each Combiner.CombineTextByEachDelimiter({“,”})(List.RemoveNulls({[Column1], [Column2]})))
in
CombinedText
Understanding the M Code
Let's take a closer look at the M code behind the Combiner.CombineTextByEachDelimiter function.
First, we create a table called `Source` using the `Table.FromRows` function. The `Source` table contains two columns called `Column1` and `Column2`.
Next, we create a new column in the `Source` table called `Combined Text` using the `Table.AddColumn` function. The `each` keyword is used to specify that we want to apply a function to each row in the `Source` table.
Inside the `each` function, we call the `Combiner.CombineTextByEachDelimiter` function and pass it a list of delimiters (`{","}`) that we want to use to separate the text values in the `Column1` and `Column2` columns.
Finally, we use the `List.RemoveNulls` function to remove any null values from the list of text values before passing it to the `Combiner.CombineTextByEachDelimiter` function. This ensures that we only combine non-null text values.
Practical Examples
Now that we understand the M code behind the Combiner.CombineTextByEachDelimiter function, let's explore some practical examples of how it can be used.
Example 1: Combining Text Values Separated by a Delimiter
Suppose you have a table with two columns called `FirstName` and `LastName`, and you want to combine the values in these columns into a single column separated by a space.
You can use the following M code to achieve this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t])),
CombinedText = Table.AddColumn(Source, “Full Name”, each Combiner.CombineTextByEachDelimiter({” “})(List.RemoveNulls({[FirstName], [LastName]})))
in
CombinedText
Example 2: Combining Text Values from Multiple Columns
Suppose you have a table with three columns called `City`, `State`, and `ZipCode`, and you want to combine the values in these columns into a single column separated by a comma.
You can use the following M code to achieve this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, State = _t, ZipCode = _t])),
CombinedText = Table.AddColumn(Source, “Address”, each Combiner.CombineTextByEachDelimiter({“, “})(List.RemoveNulls({[City], [State], [ZipCode]})))
in
CombinedText
Example 3: Combining Text Values with Quotes
Suppose you have a table with three columns called `FirstName`, `LastName`, and `EmailAddress`, and you want to combine the values in these columns into a single column separated by a comma and enclosed in quotes.
You can use the following M code to achieve this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, EmailAddress = _t])),
CombinedText = Table.AddColumn(Source, “Contact Info”, each Combiner.CombineTextByEachDelimiter({“, “}, QuoteStyle.Csv)(List.RemoveNulls({[FirstName], [LastName], [EmailAddress]})))
in
CombinedText
The Combiner.CombineTextByEachDelimiter function is a powerful tool in Power Query that allows you to combine text values in a column separated by one or more delimiters of your choice. By understanding the M code behind this function, you can create custom transformations that will help you clean and reshape your data in new and interesting ways.