Forum Discussion

michaelsp's avatar
michaelsp
Frequent Visitor
2 years ago

Multiple Fact Table Memory Issues

Hi,   I have 3 fact tables (leg1,leg2,leg3) each of which have a location1 and location2 as well as some other values that I have in slicers.   I connect those tables together using 4 identical d...
michaelsp's avatar
michaelsp
Frequent Visitor
2 years ago

I guess thats a better naming convention.  In my original data the leg tables all have the same format so I carried that over.

lbendlin's avatar
lbendlin
Super User
2 years ago

Doesn't really matter. Here is the general approach:

 

let
    Source = leg1,
    #"Grouped Rows" = Table.Group(Source, {"locationA", "locationB"}, {{"X1", each List.Average([X1]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (k)=> Table.SelectRows(leg2, each [locationB]=k[locationB])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"locationC", "X2"}, {"locationC", "X2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"locationA", "locationB", "locationC", "X1", "X2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"locationC", Int64.Type}, {"X2", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"locationA", "locationB", "locationC"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Average([X2]), type nullable number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", (k)=> Table.SelectRows(leg3, each [locationC]=k[locationC])),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"locationD", "X3"}, {"locationD", "X3"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded Custom1",{"locationA", "locationB", "locationC", "locationD", "X1", "X2", "X3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"locationD", Int64.Type}, {"X3", Int64.Type}}),
    #"Grouped Rows2" = Table.Group(#"Changed Type1", {"locationA", "locationB", "locationC", "locationD"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Max([X2]), type nullable number}, {"X3", each List.Average([X3]), type nullable number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "total", each [X1]+[X2]+[X3],type number)
in
    #"Added Custom2"

 

You can paint "Go Faster" stripes on it by using Table.Buffer for each of the tables.

 

let
    Source = Table.Buffer(leg1),
    #"Grouped Rows" = Table.Group(Source, {"locationA", "locationB"}, {{"X1", each List.Average([X1]), type nullable number}}),
    Source2 = Table.Buffer(leg2),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (k)=> Table.SelectRows(Source2, each [locationB]=k[locationB])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"locationC", "X2"}, {"locationC", "X2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"locationA", "locationB", "locationC", "X1", "X2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"locationC", Int64.Type}, {"X2", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"locationA", "locationB", "locationC"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Average([X2]), type nullable number}}),
    Source3 = Table.Buffer(leg3),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", (k)=> Table.SelectRows(Source3, each [locationC]=k[locationC])),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"locationD", "X3"}, {"locationD", "X3"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Expanded Custom1",{"locationA", "locationB", "locationC", "locationD", "X1", "X2", "X3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"locationD", Int64.Type}, {"X3", Int64.Type}}),
    #"Grouped Rows2" = Table.Group(#"Changed Type1", {"locationA", "locationB", "locationC", "locationD"}, {{"X1", each List.Max([X1]), type nullable number}, {"X2", each List.Max([X2]), type nullable number}, {"X3", each List.Average([X3]), type nullable number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "total", each [X1]+[X2]+[X3],type number)
in
    #"Added Custom2"

You could also experiment with Table.AddKey  for the join columns.

 

See attached