Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to 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!
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.
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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAttach 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! |