Forum Discussion
undefined
Hello everyone,
I want to create a table that has only the year and month between 2 dates
2019-12-1 2024-12-31
my table needs to be like this
could someone please tell me what should I do
- 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
8 Replies
- Nathaniel_CCommunity Champion
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- Nathaniel_CCommunity Champion
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_chFrequent Visitor
Thank you very much this is so helpful but could you please tell me how to create it Dax and not power query
- AlexisOlsonSuper User
Another possible construction:
let Start = #date(2019,12,1), End = #date(2024,12,31), Years = {Date.Year(Start)..Date.Year(End)}, #"Converted to Table" = Table.FromList(Years, Splitter.SplitByNothing(), {"Year"}), #"Added Months" = Table.AddColumn(#"Converted to Table", "Month", each {1..12}), #"Expanded Month" = Table.ExpandListColumn(#"Added Months", "Month"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Month",{{"Year", Int64.Type}, {"Month", Int64.Type}}), #"Added Date" = Table.AddColumn(#"Changed Type", "Date", each #date([Year], [Month], 1), type date), #"Filtered Rows" = Table.SelectRows(#"Added Date", each [Date] >= Start and [Date] <= End), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "MonthName", each Date.MonthName([Date]), type text) in #"Added Custom"You can keep or delete the extra columns as you wish.
- rima_chFrequent Visitor
Thank you a lot this is what I need but could you please tell me how to create it Dax and not power query
- AlexisOlsonSuper User
DAX is a bit simpler in this case:
MonthlyCalendar = ADDCOLUMNS ( FILTER ( CALENDAR ( DATE ( 2019, 12, 1 ), DATE ( 2024, 12, 31 ) ), DAY ( [Date] ) = 1 ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "mmmm" ) )
- AnonymousNot applicable
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