AdoDotNet.DataSource
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
- Logical.ToText
N
- Name
- Number.Abs
- Number.Acos
- Number.Asin
- Number.Atan
- Number.Atan2
- Number.BitwiseAnd
- Number.BitwiseNot
- Number.BitwiseOr
- Number.BitwiseShiftLeft
- Number.BitwiseShiftRight
- Number.BitwiseXor
- Number.Combinations
- Number.Cos
- Number.Cosh
- Number.Exp
- Number.Factorial
- Number.From
- Number.FromText
- Number.IntegerDivide
- Number.IsEven
- Number.IsNaN
- Number.IsOdd
- Number.Ln
- Number.Log
- Number.Log10
- Number.Mod
- Number.Permutations
- Number.Power
- Number.Random
- Number.RandomBetween
- Number.Round
- Number.RoundAwayFromZero
- Number.RoundDown
- Number.RoundTowardZero
- Number.RoundUp
- Number.Sign
- Number.Sin
- Number.Sinh
- Number.Sqrt
- Number.Tan
- Number.Tanh
- Number.ToText
O
P
R
- RData.FromBinary
- Record.AddField
- Record.Combine
- Record.Field
- Record.FieldCount
- Record.FieldNames
- Record.FieldOrDefault
- Record.FieldValues
- Record.FromList
- Record.FromTable
- Record.HasFields
- Record.RemoveFields
- Record.RenameFields
- Record.ReorderFields
- Record.SelectFields
- Record.ToList
- Record.ToTable
- Record.TransformFields
- Replacer.ReplaceText
- Replacer.ReplaceValue
- RowExpression.Column
- RowExpression.From
- RowExpression.Row
S
- Salesforce.Data
- Salesforce.Reports
- SapBusinessWarehouse.Cubes
- SapHana.Database
- SharePoint.Contents
- SharePoint.Files
- SharePoint.Tables
- Single.From
- Soda.Feed
- Splitter.SplitByNothing
- Splitter.SplitTextByAnyDelimiter
- Splitter.SplitTextByCharacterTransition
- Splitter.SplitTextByDelimiter
- Splitter.SplitTextByEachDelimiter
- Splitter.SplitTextByLengths
- Splitter.SplitTextByPositions
- Splitter.SplitTextByRanges
- Splitter.SplitTextByRepeatedLengths
- Splitter.SplitTextByWhitespace
- SqlExpression.SchemaFrom
- SqlExpression.ToExpression
- Sql.Database
- Sql.Databases
- Sybase.Database
T
- Tables.GetRelationships
- Table.AddColumn
- Table.AddFuzzyClusterColumn
- Table.AddIndexColumn
- Table.AddJoinColumn
- Table.AddKey
- Table.AddRankColumn
- Table.AggregateTableColumn
- Table.AlternateRows
- Table.ApproximateRowCount
- Table.Buffer
- Table.Column
- Table.ColumnCount
- Table.ColumnNames
- Table.ColumnsOfType
- Table.Combine
- Table.CombineColumns
- Table.CombineColumnsToRecord
- Table.ConformToPageReader
- Table.Contains
- Table.ContainsAll
- Table.ContainsAny
- Table.DemoteHeaders
- Table.Distinct
- Table.DuplicateColumn
- Table.ExpandListColumn
- Table.ExpandRecordColumn
- Table.ExpandTableColumn
- Table.FillDown
- Table.FillUp
- Table.FilterWithDataTable
- Table.FindText
- Table.First
- Table.FirstN
- Table.FirstValue
- Table.FromColumns
- Table.FromList
- Table.FromPartitions
- Table.FromRecords
- Table.FromRows
- Table.FromValue
- Table.FuzzyGroup
- Table.FuzzyJoin
- Table.FuzzyNestedJoin
- Table.Group
- Table.HasColumns
- Table.InsertRows
- Table.IsDistinct
- Table.IsEmpty
- Table.Join
- Table.Keys
- Table.Last
- Table.LastN
- Table.MatchesAllRows
- Table.MatchesAnyRows
- Table.Max
- Table.MaxN
- Table.Min
- Table.MinN
- Table.NestedJoin
- Table.Partition
- Table.PartitionValues
- Table.Pivot
- Table.PositionOf
- Table.PositionOfAny
- Table.PrefixColumns
- Table.Profile
- Table.PromoteHeaders
- Table.Range
- Table.RemoveColumns
- Table.RemoveFirstN
- Table.RemoveLastN
- Table.RemoveMatchingRows
- Table.RemoveRows
- Table.RemoveRowsWithErrors
- Table.RenameColumns
- Table.ReorderColumns
- Table.Repeat
- Table.ReplaceErrorValues
- Table.ReplaceKeys
- Table.ReplaceMatchingRows
- Table.ReplaceRelationshipIdentity
- Table.ReplaceRows
- Table.ReplaceValue
- Table.ReverseRows
- Table.RowCount
- Table.Schema
- Table.SelectColumns
- Table.SelectRows
- Table.SelectRowsWithErrors
- Table.SingleRow
- Table.Skip
- Table.Sort
- Table.Split
- Table.SplitAt
- Table.SplitColumn
- Table.ToColumns
- Table.ToList
- Table.ToRecords
- Table.ToRows
- Table.TransformColumnNames
- Table.TransformColumns
- Table.TransformColumnTypes
- Table.TransformRows
- Table.Transpose
- Table.Unpivot
- Table.UnpivotOtherColumns
- Table.View
- Table.ViewError
- Table.ViewFunction
- Teradata.Database
- Text.AfterDelimiter
- Text.At
- Text.BeforeDelimiter
- Text.BetweenDelimiters
- Text.Combine
- Text.Contains
- Text.End
- Text.EndsWith
- Text.From
- Text.FromBinary
- Text.InferNumberType
- Text.Insert
- Text.Length
- Text.Lower
- Text.Middle
- Text.NewGuid
- Text.PadEnd
- Text.PadStart
- Text.PositionOf
- Text.PositionOfAny
- Text.Proper
- Text.Range
- Text.Remove
- Text.RemoveRange
- Text.Repeat
- Text.Replace
- Text.ReplaceRange
- Text.Reverse
- Text.Select
- Text.Split
- Text.SplitAny
- Text.Start
- Text.StartsWith
- Text.ToBinary
- Text.ToList
- Text.Trim
- Text.TrimEnd
- Text.TrimStart
- Text.Upper
- Time.EndOfHour
- Time.From
- Time.FromText
- Time.Hour
- Time.Minute
- Time.Second
- Time.StartOfHour
- Time.ToRecord
- Time.ToText
- Transform operations
- Trigonometry
- Type.AddTableKey
- Type.ClosedRecord
- Type.Facets
- Type.ForFunction
- Type.ForRecord
- Type.FunctionParameters
- Type.FunctionRequiredParameters
- Type.FunctionReturn
- Type.Is
- Type.IsNullable
- Type.IsOpenRecord
- Type.ListItem
- Type.NonNullable
- Type.OpenRecord
- Type.RecordFields
- Type.ReplaceFacets
- Type.ReplaceTableKeys
- Type.TableColumn
- Type.TableKeys
- Type.TableRow
- Type.TableSchema
- Type.Union
V
- Value.Add
- Value.Alternates
- Value.As
- Value.Compare
- Value.Divide
- Value.Equals
- Value.Expression
- Value.Firewall
- Value.FromText
- Value.Is
- Value.Lineage
- Value.Metadata
- Value.Multiply
- Value.NativeQuery
- Value.NullableEquals
- Value.Optimize
- Value.RemoveMetadata
- Value.ReplaceMetadata
- Value.ReplaceType
- Value.Subtract
- Value.Traits
- Value.Type
- Value.VersionIdentity
- Value.Versions
- Value.ViewError
- Value.ViewFunction
- Variable.Value
Overview of AdoDotNet.DataSource
AdoDotNet.DataSource is a Power Query M function that allows users to connect to various data sources using .NET data providers. The function takes two arguments, the first being a connection string that specifies the data source, and the second being a query that retrieves the data from the source.
The function uses the .NET Framework’s System.Data.OleDb.OleDbConnection class to connect to the data source. This class provides a way to connect to a variety of data sources, including Microsoft Access, Excel, SQL Server, and Oracle.
Syntax of AdoDotNet.DataSource
The syntax of AdoDotNet.DataSource is as follows:
AdoDotNet.DataSource(connectionString as text, query as text) as table
The first argument, connectionString, is a text value that specifies the connection string to the data source. The second argument, query, is a text value that specifies the query to retrieve data from the data source. The function returns a table that contains the retrieved data.
Examples of AdoDotNet.DataSource
Here are some examples of how AdoDotNet.DataSource can be used:
Example 1: Connect to a SQL Server database
let
connectionString = "Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;",
query = "SELECT FROM Customers",
Source = AdoDotNet.DataSource(connectionString, query)
in
Source
This example connects to a SQL Server database using the SQLNCLI11 provider. The connection string specifies the server address, database name, username, and password. The query retrieves all rows from the Customers table.
Example 2: Connect to an Excel file
let
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:myFoldermyExcelFile.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';",
query = "SELECT FROM [Sheet1$]",
Source = AdoDotNet.DataSource(connectionString, query)
in
Source
This example connects to an Excel file using the Microsoft.ACE.OLEDB.12.0 provider. The connection string specifies the file path and the extended properties of the Excel file. The query retrieves all rows from the Sheet1 worksheet.
AdoDotNet.DataSource is a powerful M function in Power Query that allows users to connect to various data sources using .NET data providers. It provides a flexible and efficient way to retrieve data from different sources and manipulate it using Power Query’s transformation capabilities. With the examples provided above, users can start leveraging this function in their data analysis and business intelligence projects.
