Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |