Forum Discussion

RyanVSS's avatar
RyanVSS
Frequent Visitor
2 years ago
Solved

Help with mquery index formula

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). 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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