Forum Discussion
Solved
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...
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