Power Query having performance issue when choose selective rows

Hello Anonymous 

welcome back 🙂

this is probably because of List.Select and passing a not buffered List into List.Select. Means that this list needs to be recalculated on every row. Here a better approach when data gets big. Add a variable where you assign a buffered list of the date-column. Use this variabl within the List.Select-function

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdFBDgQhCATAv8x5E6TRQd8ymf9/Y/GwsXuPFSG08DyXm4ehoV3vZwtNNI9g3ljoR2GLgWAFtXW7GTy7y+xSHg3rDHfRZGEc3eaMKS9SB4joN8mJUxKnJE5JPC0YvLqSFPIiq40iL0uGdxakkC9Vw4bIoVxC0Af3ZkVL5H+lIeRD18gUcfid55f+/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    YourPreviousStep = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    BufferedListOfDates = List.Buffer(YourPreviousStep[Date]),
    SelectMonthDatesAndLastDaysOfMonth = Table.SelectRows
    (
        YourPreviousStep,
        (sel)=> if Date.IsInCurrentMonth(sel[Date]) then true else if sel[Date]> Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()), -4)) then  List.Max(List.Select(BufferedListOfDates, each Date.Month(_)= Date.Month(sel[Date]) and Date.Year(_)= Date.Year(sel[Date])))=sel[Date] else false
    )
in
    SelectMonthDatesAndLastDaysOfMonth

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hello Anonymous 

I saw that you didn't use the buffered list in the List.Select-function try this and tell me if it's still slow

BufferedListOfDates = List.Buffer(#"Renamed Columns"[Selected Extract Run Date]),
#"Converted to Table" = Table.FromList(BufferedListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns1" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Selected Extract Run Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Selected Extract Run Date", type date}}),
SelectMonthDatesAndLastDaysOfMonth = Table.SelectRows
(
#"Renamed Columns",
(sel)=> if Date.IsInCurrentMonth(sel[Selected Extract Run Date]) then true else if sel[Selected Extract Run Date]> Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()), -4)) then List.Max(List.Select(BufferedListOfDates , each Date.Month(_)= Date.Month(sel[Selected Extract Run Date]) and Date.Year(_)= Date.Year(sel[Selected Extract Run Date])))=sel[Selected Extract Run Date] else false
)
in
SelectMonthDatesAndLastDaysOfMonth

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy