Power Query too slow to load when adding a calculated column from another table

[gmarinov]

Hi There,

I have two tables:

PaymentsTable which has a list of all payments and their amounts plus SubscriptionID which dentifies which subscription a payment relates to.

SubscriptionsTable has a list of all subscribtion plans

In the SubscriptionsTable I have added a custom "sumif" column that adds the total payments for each subscription. The query looks as follows:

=Table.AddColumn(Source, "TotalPayments", each 
     List.Sum(
         Table.SelectRows(
             PaymentsTable,
             (InnerTable) => InnerTable[SubscriptionID] = [SubscriptionID]
         )
    [Amount])

 After that step I filter out all subscriptions where TotalPayments = 0 or blank.

This gives me a list of active (paying) subscriptions where I can do calculations to identify new/cancelled subscriptions in a given month, attrition rates etc.

The problem I have is that the PaymentsTable has millions of rows and the query is really slow to load as for every single subscription row in the SubscriptionTable it seems to be evaluating all millions of rows from the PaymentsTable.

Is there a way to optimise this query to run quicker?

Thanks!!

[KNP · Accepted solution]

Hi George,

Would be good if you could share a screenshot of your data model with relationships.

Also, some sample data if you can.

Avoid calculated columns if you can. They're often not necessary and just end up bloating your model.

Create a new measure...

Payment Amount = SUM(PaymentsTable[Amount])

Drop that in your visual and then we can figure out what filters/other measures you need.

Regards,

Kim

[KNP]

It's a little difficult to be certain without seeing your model and fully understanding the reason, but, I would have thought it would be a more appropriate as a DAX measure than a column in PQ. It will almost certainly perform better.

Any reason it needs to be done in Power Query?

[gmarinov]

Hi KNP,

The reason I did it in Power Query is that i wanted to clean the subscriptions table before it is loaded for the report users.

I did the total payments calculation as DAX column instead and it worked a treat! The only thing is that in order to clean the data, report users need to add a page filter to exclude Subscriptions where TotalPayments are 0 or (blank). Do you know if there is a way in DAX to filter the table with a measure so they don't have to do page filters/slicers?

Kind regards,

George

[KNP · Accepted solution]

Hi George,

Would be good if you could share a screenshot of your data model with relationships.

Also, some sample data if you can.

Avoid calculated columns if you can. They're often not necessary and just end up bloating your model.

Create a new measure...

Payment Amount = SUM(PaymentsTable[Amount])

Drop that in your visual and then we can figure out what filters/other measures you need.

Regards,

Kim

[gmarinov · Accepted solution]

Thanks Kim,

I did what you said with an added filters for the SubscriptionID:

PaymentsTotal =

CALCULATE(

    SUM('PaymentsTable'[Amount]),

    FILTER(

        ALL(PaymentsTable')

        ,'PaymentsTable'[SubscriptionID] IN VALUES ('SubscriptionsTable'[SubscriptionID])

    )

)

I am then using the DonationsTotal measure as a nested filter in my other measures:

CALCULATE(

    [OTHER MEASURES]

    ,FILTER(

        'SubscriptionsTable'

        ,AND(

            'SubscriptionsTable'[PaymentsTotal]>0

            ,ISBLANK('SubscriptionsTable'[PaymentsTotal])=FALSE()

        )

    )

That seems to be working and i will accept your suggestion as a solution.

Many thanks for your help!

[jbwtp]

Hi @gmarinov,

Would this be merge? When expanding the columns after the merge switch to Aggregated and choose "Sum":

This is the example in the code:

let
    Subs = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSRSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subs = _t]),
    Pmts = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YlWcgKxDSFsZxDbCMJ2AbItIUxk5WAlpkB2LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subs = _t, Payment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Pmts,{{"Subs", type text}, {"Payment", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(Subs, {"Subs"}, #"Changed Type", {"Subs"}, "Custom1", JoinKind.LeftOuter),
    #"Aggregated Custom1" = Table.AggregateTableColumn(#"Merged Queries", "Custom1", {{"Payment", List.Sum, "Sum of Payment", type number}})
in
    #"Aggregated Custom1"

Kind regards,

John

[gmarinov]

Thanks John,

Unfortunately the merge query is also agonisingly slow. It even didn't load the preview.

George