Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.

[adamb123]

Hi,

I am getting this unspecific error on my syntax in Advanced Editor in Power Query. I edited filter directly in Advanced Editor (I want to filter file path based on the current date) and I got the green tick saying "No syntax errors have been detected.", but when I save & close advanced editor i get error: 

Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]

The query looks like this:

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each
Text.Contains([Folder Path], Text.Combine({Number.ToText(Date.Year(Date.AddMonths(DateTime.LocalNow(), 0)))},{"/"},{if Date.Month(Date.AddMonths(DateTime.LocalNow(), 0)) < 10
then Text.Combine({"0"},{Number.ToText(Date.Month(Date.AddMonths(DateTime.LocalNow(), 0)))})
else Number.ToText(Date.Month(Date.AddMonths(DateTime.LocalNow(), 0)))}))
or
Text.Contains([Folder Path], Text.Combine({Number.ToText(Date.Year(Date.AddMonths(DateTime.LocalNow(), -1)))},{"/"},{if Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)) < 10
then Text.Combine({"0"},{Number.ToText(Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)))})
else Number.ToText(Date.Month(Date.AddMonths(DateTime.LocalNow(), -1)))})))
in
#"Filtered Rows1"

Can you please help me find mistake in my syntax? It's a pity that PBI doesn't underline or highlight the bad syntax in this case.

[AlienSx · Accepted solution]

Hi, @adamb123 Text.Combine expects as list of text values like Text.Combine({"text1", "text2"}) while your code is like Text.Combine({"text1"}, {"text2"}, {"text3"})

[adamb123]

This was the case.

Thank you so much!

[jennratten · Accepted solution]

Hello - it's the Text.Combine statement.  It accepts a list of text to be combined and an optional delimiter character as text, which would be 1 or 2 arguments. Sometimes it helps to break up the script with indentions.

Text.Combine(texts as list, optional separator as nullable text) as text

Here is the explaination of your actual script....the same comments apply to the second Text.Combine statement.

If you want to filter the rows for results that include a particular date within a text column, you can do it like this, which saves the current date to a variable and then selects rows which contain the date in that format.  You can modify the date variable to meet your needs, or if you can provide an example of what your dates look like and an example of what you are trying to filter for, I'll be happy to help some more.  Pls let me know if you have any questions.

    #"Filtered Rows" = Table.SelectRows(
        #"Added Custom", 
            let CurrentDate = Date.ToText ( Date.From ( DateTime.FixedLocalNow() ), "yyyyMMdd" ) in
            each Text.Contains([Custom], CurrentDate)
    )

[adamb123]

Thank you for your answer, now my script is working.

Ideally I would like to filter only Month To Date folders. I have folder structure like .../yyyy/MM/dd/.... in ASDL 2. So if today is 2023/08/21 I want to load all the files from 2023/07/21 to 2023/08/21. But since the Folder path column in ADSL 2 loading is text, I'm not sure how to easily do this dynamic filtering.

[jennratten]

I recommend you add a new column to the table which extracts the date portion of the folder path, then convert it to a date and filter the table for rows in the desired date range.

[AlienSx · Accepted solution]

Hi, @adamb123 Text.Combine expects as list of text values like Text.Combine({"text1", "text2"}) while your code is like Text.Combine({"text1"}, {"text2"}, {"text3"})