Tables.GetRelationships
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
How Tables.GetRelationships() Works
The Tables.GetRelationships() function is part of the M language syntax that is used in Power Query. This function is responsible for extracting all the relationships between tables in a workbook or a data model. The function takes a single argument, which is the name of the table or data model that you want to extract the relationships from. The function then returns a table that contains the following columns:
– Table: The name of the source table that contains the relationship
– Column: The name of the column in the source table that is part of the relationship
– Related Table: The name of the target table that is part of the relationship
– Related Column: The name of the column in the target table that is part of the relationship
Here is an example of how the Tables.GetRelationships() function works:
let
Source = Excel.Workbook(File.Contents("C:MyWorkbook.xlsx"), null, true),
Relationships = Source{[Name="Model"]}[Content]{0}[Tables]{0}[Tables.GetRelationships]()
in
Relationships
In this example, the function extracts all the relationships from the table named “Model” in an Excel workbook located at “C:MyWorkbook.xlsx”. The result of the function is a table that contains the columns described above.
Usage of Tables.GetRelationships()
The Tables.GetRelationships() function is a very useful tool for managing and transforming data in Power Query. Here are some of the common use cases for this function:
1. Understanding the Data Model
When working with a complex data model in Power BI, it is often important to understand the relationships between tables. The Tables.GetRelationships() function can be used to extract all the relationships in a data model and present them in a clear and concise format. This information can be used to gain a deeper understanding of the data model and identify potential issues or improvements.
2. Creating Calculated Columns and Measures
The Tables.GetRelationships() function can also be used to create calculated columns and measures in Power BI. Calculated columns and measures often require information about the relationships between tables, and the Tables.GetRelationships() function can be used to extract this information and use it in calculations. This can improve the accuracy and performance of calculated columns and measures.
3. Data Cleansing and Transformation
The Tables.GetRelationships() function can also be used for data cleansing and transformation in Power Query. For example, you may want to identify all the tables in a data model that are not related to any other tables. This information can be extracted using the Tables.GetRelationships() function and used to identify redundant tables that can be removed from the data model.
The Tables.GetRelationships() function is a powerful tool for managing and transforming data in Power Query. It is used to extract the relationships between tables in a workbook or data model and can be used for a variety of purposes, such as understanding the data model, creating calculated columns and measures, and data cleansing and transformation. The M code behind the Tables.GetRelationships() function is simple and easy to understand, and it can be modified to suit the specific needs of your data analysis tasks.
