cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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"

 

 

 

 

1 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!

View solution in original post

6 REPLIES 6
Super User III
Super User III

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.

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!

View solution in original post

Super User III
Super User III

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

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?

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors