Table relations

[davidz106]

Hi,

I have a below structure in my power BI in form of queries:

-table A

-table B

-table C

-table D

Each of those contains Column X (type text) where some of the values can be the same as in one (or more) of the other tables or there are no same values. 

Currently, I combine all of the values in Column X (form all tables) in one list/table and then relate each table to this combined query. This means I am basically duplicating queries (and causing unnecessary workload?) to make a combined list.

Is there a better and more efficient way to do this, especcialy if we have N tables (relating each to each would take a long time also)?

[artpil]

Hi,

Tats interesting question. I did test on 4 excel tables 100'000 rows ech and creating list of unique vales takes couple of seconds and merging 4 tables and expanding it takes maybe 20s. I think this is pretty fast.

Here's the code I used for testing:

let
    Source = "a",
    TableA = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TableB = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    TableC = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    TableD = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Custom1 =List.Distinct( List.Combine({TableA[X],TableB[X],TableC[X],TableD[X]})),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "UniqueValues"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"UniqueValues"}, TableA, {"X"}, "TableA", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"UniqueValues"}, TableB, {"X"}, "TableB", JoinKind.LeftOuter),
    Custom2 = Table.NestedJoin(#"Merged Queries1", {"UniqueValues"}, TableC, {"X"}, "TableC", JoinKind.LeftOuter),
    Custom3 = Table.NestedJoin(#"Custom2", {"UniqueValues"}, TableD, {"X"}, "TableD", JoinKind.LeftOuter),
    #"Expanded TableA" = Table.ExpandTableColumn(Custom3, "TableA", {"y"}, {"yA"}),
    Custom4 = Table.ExpandTableColumn(#"Expanded TableA", "TableB", {"y"}, {"yB"}),
    Custom5 = Table.ExpandTableColumn(Custom4, "TableC", {"y"}, {"yC"}),
    Custom6 = Table.ExpandTableColumn(Custom5, "TableD", {"y"}, {"yD"})
in
    Custom6

Crucial steps are Custom1 where I create list of unique values and "Convrt to Table" where I change it into a table column.

Hope it helps.

Artur

[idage]

Hi, just a question, in this way you will louse informations contained in colums of duplicates values of each tab...right?

[BA_Pete · Accepted solution]

Hi @davidz106 ,

It sounds to me like you're doing exactly the right thing here i.e. dynamically creating a dimension table to control N number of fact tables which contain that dimension.

I think the only extra efficiency you might get would be in the code you're using to generate the dimension table, rather than the way in which you've gone about solving the issue.

The most efficient code I can think of to create this dimension table would be:

let
    Source =
    Table.Distinct(
        Table.SelectColumns(
            Table.Combine({tableA, tableB, tableC, tableD}),
            {"Column X"}
        )
    )
in
    Source

If your tables A-D aren't similar, and only have this one column X in common, then you may want to expand the code slightly, but relieve some pressure off the mashup engine:

let
    Source =
    Table.Distinct(
        Table.Combine(
            {
                Table.SelectColumns(tableA, {"Column X"}),
                Table.SelectColumns(tableB, {"Column X"}),
                Table.SelectColumns(tableC, {"Column X"}),
                Table.SelectColumns(tableD, {"Column X"})
            }
        )      
    )
in
    Source

Pete