Forum Discussion

gmarinov's avatar
gmarinov
Frequent Visitor
4 years ago
Solved

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

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 ...
Need Help
  • KNP's avatar
    KNP
    4 years ago

    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's avatar
    gmarinov
    4 years ago

    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's avatar
jbwtp
Memorable Member
4 years ago

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's avatar
gmarinov
Frequent Visitor
4 years ago

Thanks John,

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

George