How to add the Calendar table from Power Pivot to Power Query?

%3CLINGO-SUB%20id%3D%22lingo-sub-1500045%22%20slang%3D%22en-US%22%3EHow%20to%20add%20the%20Calendar%20table%20from%20Power%20Pivot%20to%20Power%20Query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500045%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20created%20a%20Calendar%20table%20using%20the%20Power%20Pivot%20and%20therefore%20it%20is%20already%20in%20the%20data%20model%20as%20well.%20However%2C%20how%20can%20I%20have%20this%20in%20my%20Power%20Query%20so%20that%20I%20can%20use%20it%20in%20the%20Excel%20to%20Pivot%20or%20add%20Slicers%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1500045%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500257%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20the%20Calendar%20table%20from%20Power%20Pivot%20to%20Power%20Query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500257%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20you're%20well%20informed%20that%20you%20create%20the%20automatic%20Calendar%20Table%20in%20the%20Design%20tab%20of%20the%20Power%20Pivot%2C%20the%20calendar%20table%20is%20independent%20of%20the%20Power%20Query.%3CBR%20%2F%3E%3CBR%20%2F%3EOnce%20you%20have%20created%20your%20Calendar%20Table%2C%20do%20click%20on%20the%20Digram%20tab%20and%20create%20one-to-many%20relationship%20between%20the%20Calendar%20Table%20(unique%20dates)%20and%20the%20Fact%20Table%20Date.%3CBR%20%2F%3E%3CBR%20%2F%3EThen%2C%20you%20can%20use%20the%20time%20intelligence%20functions%20and%20finally%2C%20you%20can%20create%20Slicer%20from%20any%20of%20the%20Calendar%20dates%20field%20in%20the%20PivotTable%20Report%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3C%2FLINGO-BODY%3E
Contributor

Hi,

 

I just created a Calendar table using the Power Pivot and therefore it is already in the data model as well. However, how can I have this in my Power Query so that I can use it in the Excel to Pivot or add Slicers? 

 

Cheers, 

 

 

 

2 Replies
Hello,

As you're well informed that you create the automatic Calendar Table in the Design tab of the Power Pivot, the calendar table is independent of the Power Query.

Once you have created your Calendar Table, do click on the Digram tab and create one-to-many relationship between the Calendar Table (unique dates) and the Fact Table Date.

Then, you can use the time intelligence functions and finally, you can create Slicer from any of the Calendar dates field in the PivotTable Report


Cheers

@Nima Mohandesan 

These days best practice is to generate calendar initially by Power Query, not to generate it by Power Pivot. That's much more flexible solution. Just google for the pattern, it shall be lot of them.