Forum Discussion
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"
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!
6 Replies
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_TableYou 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.
Hi cathoms
Here's a sample Excel file with a table called Dates
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!
