Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cathoms
Helper V
Helper V

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
PhilipTreacy
Super User
Super User

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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
CNENFRNL
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors