Date Table

[Fredollar]

I created a date table using dax.I wanted to edit it in power query i notice there was no option for me to do that.Please is that how it works or there is something am not doing right?

[Fredollar]

Thanks so much @goncalogeraldes that explaination solve it for me.

Appreciate.

[goncalogeraldes · Accepted solution]

Hello there @Fredollar ! Calculated Columns or Calculated tables through DAX are not available in Power Query and that is because PQ "comes before" all your modelling through DAX or other tools. That is to say that since PQ is the step of preparing and transforming our data for later on using it for modelling in Power BI, all transformations you perform in PQ are available in Power BI but the other way around is not true (PQ -> Power BI but not PBI -> PQ). If you need to create a Calendar Table and use it PQ you must create it in PQ. Here is an example of M code for generating a Calendar Table:

let
  StartDate = #date(Date.Year(DateTime.LocalNow()) - 2, 1, 1),
  EndDate = Date.From(DateTime.LocalNow()),
  NumberOfDays = Duration.Days( EndDate - StartDate ),
  Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
  To_Table = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  Renamed_Columns = Table.RenameColumns(To_Table, {{"Column1", "FullDateAlternateKey"}}),
  Changed_Type = Table.TransformColumnTypes(Renamed_Columns, {{"FullDateAlternateKey", type date}}),
  Insert_Year = Table.AddColumn(Changed_Type, "Year", each Date.Year([FullDateAlternateKey]), type number),
  Insert_Month = Table.AddColumn(Insert_Year, "Month", each Date.Month([FullDateAlternateKey]), type number),
  Insert_Month_Name = Table.AddColumn(Insert_Month, "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
  Insert_Quarter = Table.AddColumn(Insert_Month_Name, "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
  Insert_Week_Year = Table.AddColumn(Insert_Quarter, "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
  Insert_Week_Month = Table.AddColumn(Insert_Week_Year, "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
  Insert_Day = Table.AddColumn(Insert_Week_Month, "Day", each Date.Day([FullDateAlternateKey]), type number),
  Insert_Day_Week = Table.AddColumn(Insert_Day, "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
  Insert_Day_Year = Table.AddColumn(Insert_Day_Week, "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
  Insert_Day_Name = Table.AddColumn(Insert_Day_Year, "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text),
  RenameColumns = Table.RenameColumns(Insert_Day_Name, {{"FullDateAlternateKey", "Date_Key"}})
in
  RenameColumns

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes