Power Query/Pivot Table data issues

[ddibble]

Hello All,

I'm struggling with memory issues when using Power Query or Power Pivot for a very large dataset. So I'm looking for ways to cut down on the 'returned' data. Here is the scenario. I have 2 tables, on called GL_Summary and one called GL_Detail. The GL_Summary has about 10,000 entries (no problem returning all of them), but the GL_Detail table has about 700,000 entries which is crazy slow to bring into Excel. There is a relationship between the two tables on a field called JournalEntryBK (it is a one to many relationship). There is a field in the GL_Summary table called AccountBK (this field is not in the GL_Detail table).

I'm trying to (via a Pivot Table or Data Model) find a way to select an AccountBK in the GL_Summary table and then return all of the corresponding entries that have a matching JournalEntryBK in the GL_Detail table. I've messed around with different ways for a few hours, but cannot figure it out. Any ideas??

 

[AlanSidman]

It would be very helpful if you provided some sample data of 10 to 15 records for each so that we can visualize and manipulate. With the sample data, mock up what you want the solution to look like so we can replicate. Do not load pictures as we cannot manipulate data in a picture. Upload sample file.

 

[ddibble]

Hey Alan, yes, I should have done that to start with! I've attached a file with some sample data in it.

 

[AlanSidman]

Is the common field Jouranl Entry BK. Is that what we should be joining on.

 

[ddibble]

Yes, that is it. Here is a better file. I cut out too many rows in the prior file so there were only a few common JournalEntryBKs.

 

[AlanSidman]

When I join the two tables on the common field, I get no matches for the Account BK

Here is my Mcode to join the two tables

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"JournalEntryBK"}, T2, {"JournalEntryBK"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"AccountName_Desc", "Credit", "Debit", "GLAccountBK", "JournalEntryBK", "TenantGLAccountBK", "Column1"}, {"AccountName_Desc", "Credit", "Debit", "GLAccountBK", "JournalEntryBK.1", "TenantGLAccountBK", "Column1"})


in
    #"Expanded Table2"

 

[ddibble]

Thanks for looking in on this Alan. I think I've explained this poorly. What I'm trying to do is find an efficient way to select an AccountBK in the GL_Summary table and have all of the corresponding JournalEntryBK entries that are also in the GL_Summary table be automatically filtered in the GL_Detail table. As an example, if I select AccountBK '37' in the GL_Summary table, JournalEntryBK '321535' is the first entry in the GL_Summary table. I'd like to find a way to have the GL_Detail table automatically filter on JournalEntryBK '321535' (along with all of the other filtered JournalEntryBK entries that are in GL_Summary table after AccountBK '37' was selected).

The kicker is that the GL_Detail pivot table is actually has over 700,000 rows so I'm trying to automatically filter it rather than manually making selections that match what was selected in GL_Summary pivot table.

 

[AlanSidman]

If I am understanding correctly, Filter on 37 and then join the two tables. I have built a parameter table where you can enter the appropriate AccountBK and then refresh the query.

 

[ddibble]

Thanks Alan, I'll give it a shot with my live data.