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
What is the BinaryFormat.Single function?
The BinaryFormat.Single function is used to convert a binary value into a single-precision floating-point number. The function takes a binary value as input and returns a floating-point number. The function is commonly used when working with data types that are not supported by Power Query, such as binary data.
Understanding the M code behind the BinaryFormat.Single function
The M code behind the BinaryFormat.Single function is fairly simple. The function takes a binary value as input and converts it into a single-precision floating-point number. Here is the M code for the BinaryFormat.Single function:
(BinaryValue as binary) =>
let
SingleValue = BinaryFormat.ByteOrder(BinaryValue, ByteOrder.LittleEndian),
Result = Number.FromBinary(SingleValue, 1),
in
Result
As you can see, the function takes a binary value as input and then performs two operations on it. First, it uses the BinaryFormat.ByteOrder function to specify the byte order of the binary value. The byte order can be either LittleEndian or BigEndian.
In LittleEndian byte order, the least significant byte of the binary value is stored first, while the most significant byte is stored last. In BigEndian byte order, the most significant byte is stored first, while the least significant byte is stored last.
After specifying the byte order, the function uses the Number.FromBinary function to convert the binary value into a floating-point number. The Number.FromBinary function takes two arguments: the binary value to convert and the number of bytes to use for the conversion. In this case, we are using a single byte to convert the binary value into a floating-point number.
Using the BinaryFormat.Single function in Power Query
Now that we understand the M code behind the BinaryFormat.Single function, let's see how we can use it in Power Query.
To use the BinaryFormat.Single function, we first need to have a binary value. Let's say we have a table that contains a column called BinaryData, which contains binary values. Here is an example of how we can use the BinaryFormat.Single function to convert the binary values into floating-point numbers:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjQwMjYxMlYyNDIwMjYxMFayUgoyMDM1ME5JNDXUNFUwNjZINzY2VjI0MDI2MTBYMjQyMDI2MTBaNkVWUlFqgQAA”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BinaryData = _t]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“BinaryData”, type binary}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “FloatData”, each BinaryFormat.Single([BinaryData])),
#”Changed Type1″ = Table.TransformColumnTypes(#”Added Custom”,{{“FloatData”, type number}})
in
#”Changed Type1″
As you can see, we first load the table into Power Query and then transform the BinaryData column into a binary data type. We then add a custom column called FloatData, which uses the BinaryFormat.Single function to convert the binary values into floating-point numbers.
Finally, we transform the FloatData column into a number data type. Once we have done this, we can load the data into Excel or Power BI and use it as needed.
The BinaryFormat.Single function is a powerful tool in Power Query that allows users to convert binary values into floating-point numbers. Understanding the M code behind this function can help users to use it more effectively and efficiently. By following the steps outlined in this article, users can use the BinaryFormat.Single function to transform their data and create powerful Excel and Power BI dashboards.