Forum Discussion
undefined
- Anonymous4 years ago
Hi rima_ch ,
Check the new table formula.
Table = distinct(SELECTCOLUMNS(CALENDAR(DATE(2019,12,1),DATE(2024,12,31)),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM")))Best Regards,
Jay
Hi rima_ch ,
Go to power query and paste this into the advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjC0VNIBsY1BHCMTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([Start])..Number.From([End])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Dates])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each Date.MonthName([Dates])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Year", type text}, {"Month", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"Year", "Month"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Year", "Month"}, {{"det", each _, type table [Year=nullable text, Month=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"det"})
in
#"Removed Columns"
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel
Hi rima_ch ,
Here are pics of steps in reverse order.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel
Advanced Editor paste codeHome tab in PQ new source blank queryIn PowerBI Transform Data on home tabFinal table
- rima_ch4 years agoFrequent Visitor
Thank you very much this is so helpful but could you please tell me how to create it Dax and not power query
- Nathaniel_C4 years agoCommunity Champion
Hi rima_ch ,
Here is a great blog from Matt Allington. https://powerbi.tips/2017/11/creating-a-dax-calendar/
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel