Forum Discussion
Multiple Fact Table Memory Issues
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Here is simplified version of the data. The actual data for leg[123] has tens of thousands of rows.
leg1
| location1 | location2 | X |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 2 | 3 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
leg2
| location1 | location2 | X |
| 2 | 1 | 1 |
| 2 | 3 | 2 |
| 3 | 2 | 3 |
| 1 | 3 | 4 |
| 2 | 3 | 5 |
leg3
| location1 | location2 | X |
| 1 | 2 | 1 |
| 2 | 3 | 2 |
| 3 | 1 | 3 |
| 3 | 2 | 4 |
| 2 | 1 | 5 |
location[ABCD]
| location | name |
| 1 | q |
| 2 | r |
| 3 | s |
Expected Results
| Location A | Location B | Location C | Location D | Avg X leg1 | Avg X leg2 | Avg X leg3 | total |
| q | r | q | r | 2.5 | 1 | 1 | 4.5 |
| q | r | s | q | 2.5 | 3.5 | 3 | 9 |
| q | r | s | r | 2.5 | 3.5 | 4 | 10 |
| q | s | r | q | 2 | 3 | 5 | 10 |
| q | s | r | s | 2 | 3 | 2 | 7 |
| r | q | s | q | 5 | 4 | 3 | 12 |
| r | q | s | r | 5 | 4 | 4 | 13 |
| r | s | r | q | 3 | 3 | 5 | 11 |
| r | s | r | s | 3 | 3 | 2 | 8 |
Shouldn't the columns in leg2 be location2 and location3 ?
I guess thats a better naming convention. In my original data the leg tables all have the same format so I carried that over.
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