Expression.Error: 1 arguments were passed to a function which expects 0.

[cathoms]

I have an Excel workbook called "Dates" and used it to create a table in Power BI called "Date_Lookup". I want to create columns for Fiscal Year, Fiscal Quarter, and Fiscale Period (i.e. sequentially numbered months starting with July, the beginning of our fiscal year). I figured out DAX for fiscal year but in seeking help user PhilipTreacy provided some M code for a blank query. I should just need to change the source. I have been trying but I keep getting errors.

What I have is below, using a made-up file path for this purpose. What am I doing wrong?

let
    Quarters = {3,3,3,4,4,4,1,1,1,2,2,2},
    Period = {7,8,9,10,11,12,1,2,3,4,5,6},
    Source = Excel.CurrentWorkbook("C:\Users\cathoms\OneDrive – My Organization\Project Areas\QMDashboard\Dates.xlsx"){[Name="Dates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each if Date.Month([Date])>6 then Date.Year([Date])+1 else Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fiscal Quarter", each Quarters{Date.Month([Date])-1}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Fiscal Period", each Period{Date.Month([Date])-1})
in
    #"Added Custom2"

[PhilipTreacy]

Hi @cathoms 

Here's a sample Excel file with a table called Dates

PQ Custom Fiscal Periods

The blue table on Sheet1 is the table named Dates.

This line loads that table from the current workbook

Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],

To load the same Excel file into PBI use this query.

Download the PBIX file.

let
    Quarters = {3,3,3,4,4,4,1,1,1,2,2,2},
    Period = {7,8,9,10,11,12,1,2,3,4,5,6},
    Source = Excel.Workbook(File.Contents("D:\temp\PQ Custom Fiscal Periods.xlsx"), null, true),
    Dates_Table = Source{[Item="Dates",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Dates_Table,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each if Date.Month([Date])>6 then Date.Year([Date])+1 else Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fiscal Quarter", each Quarters{Date.Month([Date])-1}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Fiscal Period", each Period{Date.Month([Date])-1})
in
    #"Added Custom2"

You'll see that these 2 lines load the file and then the Dates table

    Source = Excel.Workbook(File.Contents("D:\temp\PQ Custom Fiscal Periods.xlsx"), null, true),
    Dates_Table = Source{[Item="Dates",Kind="Table"]}[Data],

Phil 

---

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[cathoms · Accepted solution]

I was still having problems implementing some of the solutions proposed so I ended up generating the additional fields in excel and just started with a data source that had the fields already. Thanks everyone for trying to help me out. Hopefully after some time I will have a better grasp on Power Query vis-à-vis Power BI and can circle back to implement what you've suggested here.

Cheers!

[CNENFRNL]

Hi, @cathoms , in fact, this expression in your code throws the error

Source = Excel.CurrentWorkbook("C:\Users\cathoms\OneDrive – My Organization\Project Areas\QMDashboard\Dates.xlsx"){[Name="Dates"]}[Content],

It's a syntax error because Excel.CurrentWorkbook requires no parameter.

Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],

Here's the reference: https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook

[cathoms]

But then I get the following Expression.Error: We couldn't find an Excel table named 'Dates'.

How do I set or indicate the current workbook?

[edhans]

@cathoms - manually connect to the Excel table using the UI and connect to the Date table. You will get code that looks like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\filepath\Book1.xlsx"), null, true),
    Dates_Table = Source{[Item="Dates",Kind="Table"]}[Data]
in
    Dates_Table

You need to put those two lines of code after the quarter/period, but before the Changed Type step, and your change type step should point to Dates_Table. My data was in an Excel table called Dates. 

See this article if you need help using this M code in your model.

[CNENFRNL]

Attach you workbook pls and I'll show you full steps. I can't make a guess out of anything.