No Syntax Errors but Query Won't Work

[KyleD87]

The Image above is of my Advanced Editor. I have no syntax errors, but get an error message after clicking done. All of the lines are necessary, and the bulk of them add custom columns to retrieve certain information for the specified column. At the bottom, is a code to "bump" the data to the top, because previously it was scattered with many empty sections in the spreadsheet. The last line is to only show dates within 5 years of the earliest date on the given spreadsheet. 

This is the error message I get when I click done. If anyone has any idea as to what is wrong here and what can be fixed, the help is appreciated. I do want to make it clear that telling me I have to delete several lines is not going to work - the codes in there I need, and removing some would completely ruin the Power Query. Nonetheless, if there is an edit I can make, or some sort of rearrangement, I would open that with open arms. Thanks

[Anonymous]

Remove the second end parentheses from the Table.ColumnNames step. Also, the "and" between the last two steps should just be a comma. And you have to then name your last step. Finally, your "in" statement should reflect the name of that new last step.

--Nate

[AlB · Accepted solution]

@KyleD87 

Update the bit from #"Removed Duplicates" onwards:

#"Removed Duplicates" = Table.Distinct(#"ClearNulls", { "DATE" } ),

LastStep_ =   Table.SelectRows(#"Removed Duplicates", each [date] >= List.Min(#"Removed Duplicates"[date]) and [date] <= Date.AddYears(List.Min(#"Removed Duplicates"[date]),5))

in

  LastStep_ 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[KyleD87]

Still getting an error message. This is the first power query ive ever done, and I dont know coding so I have been running intop uncountable amounts of trouble which can be frustrating. I just want this to work! Im close to paying you to help me out on this code, but this is my last step I just need to correct this syntax error

[AlB]

@KyleD87 

You didn't copy correctly.  Substitute all that you have from #"Removed Duplicates" onwards with the code I provided on my previous post. Otherwise share the pbix and we'll fix it quickly

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@KyleD87 

The error message is quite clear. In your last step you are attempting an AND between two tables. That does not make sense. AND is applied to arguments of type logical.  What exactly are you trying to do in that step?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[KyleD87]

#Filtered Rows onwards is the code for "de-scattering" my data, which bumps the data up to the top cleaning it up. 

On reddit, I was told to do this:

"At any rate, this will go five years from the earliest date in your [date] column.

= Table.SelectRows(#"Added Custom", each [date] >= List.Min(#"Added Custom"[date]) and [date] <= Date.AddYears(List.Min(#"Added Custom"[date]),5))

Replace #"Added Custom" with your previous step and [date] with your date column. Pay close attention to how you are adding this, and if it is not the last step it needs a comma after it. I tested it from 1/1/2020 and the last date to show will be 1/1/2025".

Every part of my code is correct and works, except for this last line. The goal for this line you are saying is incorrect is to return dates within 5 years of the earliest date in the spreadsheet. The earliest date will vary, so I need all the date within 5 years, nothing later. I just can't figure out what is wrong with the code, so if theres another way to write this which would generate the same results that would be great. Thanks