Forum Discussion
rima_ch
4 years agoFrequent Visitor
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...
- 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
Nathaniel_C
Community Champion
4 years agoHi 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_C
Community Champion
4 years agoHi 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