Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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
Proud to be a Datanaut!
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
Hi, just a question, in this way you will louse informations contained in colums of duplicates values of each tab...right?
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.