Power Query having performance issue when choose selective rows

[Anonymous]

hihi profs,

 need help on power query to choose rows... how to choose all date for the current month and max date for previous months

meaning to choose 12/07/2020, 12/14/2020, 12/21/2020, and 11/30/2020, 10/26/2020 and so on in this table

Index

1/13/2020

1/20/2020

1/28/2020

2/10/2020

2/24/2020

3/9/2020

3/23/2020

3/30/2020

4/6/2020

4/13/2020

4/20/2020

4/27/2020

5/4/2020

5/11/2020

5/18/2020

5/25/2020

6/1/2020

6/8/2020

6/15/2020

6/22/2020

6/29/2020

7/6/2020

7/13/2020

7/20/2020

7/27/2020

8/3/2020

8/10/2020

8/17/2020

8/24/2020

8/31/2020

9/7/2020

9/14/2020

9/21/2020

9/28/2020

10/5/2020

10/12/2020

10/19/2020

10/26/2020

11/2/2020

11/9/2020

11/16/2020

11/23/2020

11/30/2020

12/7/2020

12/14/2020

12/21/2020

last time got one prof, helped to solved it, but now I have many dates, appear to have performance issue...

here is the prof solution

this code now filters current month + max date of last 4 months

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"),
    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(YourPreviousStep[Date], each Date.Month(_)= Date.Month(sel[Date]) and Date.Year(_)= Date.Year(sel[Date])))=sel[Date] else false
    )
in
    SelectMonthDatesAndLastDaysOfMonth

Hi Prof, 

@Jimmy801

thanks for the help

[Jimmy801 · Accepted solution]

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

[Anonymous]

Hi prof after the list buffer, i think need to convert table, since is table select rows, I used the convert to table then ok

thank you 

@Jimmy801 

[Jimmy801]

Hello @Anonymous 

so it worked?

BR

Jimmy

[Anonymous]

Hi prof,

just now i refresh the preview... still take long time... but I think fast than not having the buffer, should be fine. btw I only have one column. may be the table select row can change to list select row?

thank you for the help

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(#"Changed Type"[Selected Extract Run Date], 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

[Jimmy801 · Accepted solution]

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

[Anonymous]

Hi Prof, 

i guess a bit fast than before, my previous thinking is that that bufferlist is wrapped into the following query, so for the list select, I choose the previous step..

thanks for your help prof

@Jimmy801 

[Jimmy801 · Accepted solution]

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

[Anonymous]

Hi prof thank for your help, i will try this.

your reply is super fast