Help with mquery index formula

[RyanVSS]

i have an index function to return the latest record attribute for each contact that is breaking when i apply it to large datasets. 

Table.RowCount(Table.SelectRows( #"Filtered Rows",(x)=>
x[createdon]>[createdon] and x[slgc_contact]=[slgc_contact] and x[Attribute]=[Attribute]))+1

The function works fine with around 10,000 rows of data (4 columns) but will crash when applied to around 30,000+ rows. The whole dataset is 100,000s of rows and growing. My question is whether this function can be broken down or changes in anyway to allow for it to scale up with my source data?  i've already cleaned the source data and piped it through a dataflow, but it doesn't make any difference to the final modelling with the above function (it's the only function in the final model). 

[Anonymous · Accepted solution]

Hi @RyanVSS ,

Please try the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3MtY3MjAyUdJRcnQEEp55aflFuakpwcn5RalAvolSrA5YmRGKMo+YUgMDI7PwYqgyY5gyQxRlwYlpqSWVweiKDPBaCVNmaEmUlYYWMGVOTjitNDRHUYThSz0zmEIzFIU4LTXFb2ksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [createdon = _t, slgc_contact = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"createdon", type date}, {"slgc_contact", type text}, {"Attribute", type text}, {"Value", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"createdon", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"slgc_contact", "Attribute"}, {{"Data", each  Table.FirstN(_,1)}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"createdon", "Value"}, {"createdon", "Value"}),
    #"Grouped Rows2" = Table.Group(#"Expanded Data", {"slgc_contact"}, {{"LatestSession", each List.Max([createdon]), type nullable date}, {"All", each _, type table [createdon=nullable date, slgc_contact=nullable text, Attribute=nullable text, Value=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows2", "All", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded All", List.Distinct(#"Expanded All"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[bhanu_gautam]

@RyanVSS , Try using below m-code

let
// Load your data
Source = YourDataSource,

// Add an Index Column
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

// Group by slgc_contact and Attribute, and get the max createdon
Grouped = Table.Group(AddIndex, {"slgc_contact", "Attribute"}, {{"MaxCreatedOn", each List.Max([createdon]), type datetime}}),

// Merge the grouped data back with the original table
Merged = Table.NestedJoin(AddIndex, {"slgc_contact", "Attribute", "createdon"}, Grouped, {"slgc_contact", "Attribute", "MaxCreatedOn"}, "GroupedData", JoinKind.Inner),

// Expand the merged table to get the latest records
Expanded = Table.ExpandTableColumn(Merged, "GroupedData", {"MaxCreatedOn"}, {"MaxCreatedOn"}),

// Filter to get only the latest records
LatestRecords = Table.SelectRows(Expanded, each [createdon] = [MaxCreatedOn])
in
LatestRecords

[RyanVSS]

.

[RyanVSS]

@ryan_mayu