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
One of Power Query’s M functions is Cube.AddAndExpandDimensionColumn. This function adds a new column to a table and expands it into multiple columns, creating new rows for each combination of other columns in the table.
Syntax of Cube.AddAndExpandDimensionColumn Function
The syntax of Cube.AddAndExpandDimensionColumn function is as follows:
Cube.AddAndExpandDimensionColumn(table as table, column as text, values as list, newColumnNames as list, optional missingValue as nullable any) as table
The parameters are:
- table: A table or a reference to a table.
- column: The name of the column to expand.
- values: A list of values to expand into new columns.
- newColumnNames: A list of column names for the new expanded columns.
- missingValue (optional): The value to replace any missing values with.
How Cube.AddAndExpandDimensionColumn Function Works
The Cube.AddAndExpandDimensionColumn function works by adding a new column to the table, then expanding it into multiple columns, creating new rows for each combination of other columns in the table.
For example, consider the following table:
Table.InlineTable({{1, “A”, 10}, {1, “B”, 20}, {2, “A”, 30}, {2, “B”, 40}})
This table has three columns: "Column1", "Column2", and "Column3". We can use the Cube.AddAndExpandDimensionColumn function to add a new column "Column4" with values "X" and "Y", and expand it into two new columns "Column5" and "Column6":
Cube.AddAndExpandDimensionColumn(Table.InlineTable({{1, “A”, 10}, {1, “B”, 20}, {2, “A”, 30}, {2, “B”, 40}}), “Column4”, {“X”, “Y”}, {“Column5”, “Column6”})
The resulting table will have six columns: "Column1", "Column2", "Column3", "Column4", "Column5", and "Column6". The "Column4" will be expanded into two columns "Column5" and "Column6", and new rows will be created for each combination of other columns in the table. The missing values will be replaced with null by default.
Use Cases of Cube.AddAndExpandDimensionColumn Function
The Cube.AddAndExpandDimensionColumn function is used to expand a column with multiple values into separate columns. This is useful in scenarios where you need to transform data from wide to long format or create a pivot table.
For example, consider a table with three columns "Region", "Product", and "Sales". We can use the Cube.AddAndExpandDimensionColumn function to expand the "Product" column into separate columns for each product:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjIwMtQ0sTVXS87PUUgnWkGxg8lKizFqWn5+Uk5yQmZyfnl+UklqSnKxJzE2NzYyMLQyMzUwtDQ3MzYyNjE2JzIvPyxKLElNSixJTDorKzQxNjM1Mjk1KSpJLEktK0opLUspNTc7MjQwMjQ1MTUzMTM7OTkwMjM1NzQwMDE7MDsxOzA7MDswOzA7MDswOzA7MDswOzA7MDswOzA7MDswOzA7MDswOzA7MDswOyMqLyo/MDBJzUjMSS0qtvLOMwq3Mz0nUS0rKzNMSUs5sLkwtjE3N7c1MDQ3MzYyJzUwMzQ1NzUyMzY3″, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Region/Product” = _t, Sales = _t])),
#”Split Column by Delimiter” = Table.SplitColumn(Source, “Region/Product”, Splitter.SplitTextByDelimiter(“/”, QuoteStyle.Csv), {“Region”, “Product”}),
#”Changed Type” = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Region”, type text}, {“Product”, type text}, {“Sales”, Int64.Type}}),
#”Added Custom” = Cube.AddAndExpandDimensionColumn(#”Changed Type”, “Product”, {“A”, “B”, “C”}, {“Sales for A”, “Sales for B”, “Sales for C”})
in
#”Added Custom”
The resulting table will have six columns: "Region", "Product", "Sales", "Product.A", "Product.B", and "Product.C". The "Product" column will be expanded into three columns "Product.A", "Product.B", and "Product.C", and new rows will be created for each combination of other columns in the table. The missing values will be replaced with null by default.
The Cube.AddAndExpandDimensionColumn function is a powerful M function in Power Query that allows you to expand a column with multiple values into separate columns. It is useful in scenarios where you need to transform data from wide to long format or create a pivot table. By understanding how this function works and its use cases, you can take advantage of its capabilities to transform and clean data.