Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
11-17-2021 11:25 AM - last edited 11-17-2021 11:26 AM
Came out of a thread, needed a Nepali calendar. Involves a Power Query Enter Data query and a DAX custom table. Photo credit Rocket Deckler!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZhNroMwDITvwrqL/AfOUvX+13hqVULjNzF24g1CqJ7aH9bE5vncgnNue2zxcwnvi+/vXHcJR3/nt9fjI+JbAIn3nRKN/wp/RQIQAZnwIhEkwZbzI9LKSRZMsgWTYsGkWjDZQf59Td1fd0pN5DBg4p0BE4+S0DLxKP97JiSTyMVLmSQLJhkkoWZCOnaOSeXipUx2EK9mcshEWCsIpGNRTbeZBAuPDagI6StuImqPRUxIx84xsfDYUEC8tu1DbY8WmBCPnStn6LGBTaITOYtbYvLz23kmFMIUk4g8VmsFMYEk1Ewyy0R2jEY0FaiZII9VMxF6LM+EdOzUkZHQVKBlklASWiZpxWNPJol07BwTNBWomQinAl7EwmMT6dipIyNZeGxSTwVAJKs9FthjJqlPMckWHptXNq8movZYxMRiKshDj1Uco9li88p7e7TAxGLzKshjtVZQhkwVTMr1Eu6P0RGTYrF5FYvNq1hsXmVu8yJMLDavgjxWzWTFY89yKunYqWO08vYqY1KFUwEvYvF1q1psXtXCY+vKVNBE1B4LrKDef90SlGPhsbt6KkAiqJJ7JkSE91ihCO+xQhF+jhWKDD3W/VcaihQLJkKP5UX2PvXrFWvKuZ7Pl3M4CxGhx45EXn8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t, #"11" = _t, #"12" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}, {"7", Int64.Type}, {"8", Int64.Type}, {"9", Int64.Type}, {"10", Int64.Type}, {"11", Int64.Type}, {"12", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Year"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}, {"Value", "DaysInMonth"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month", Int64.Type}})
in
#"Changed Type1"
Bikram Sambat =
VAR __Table =
ADDCOLUMNS(
GENERATE(
'Table',
GENERATESERIES(1, CALCULATE(MAX('Table'[DaysInMonth])), 1)
),
"__Index",[Year]*10000+[Month]*100+[Value]
)
VAR __Table1 =
ADDCOLUMNS(
__Table,
"__ADDate",DATE(1943, 4, 14) + COUNTROWS(FILTER(__Table,[__Index]<EARLIER([__Index])))
)
RETURN
SELECTCOLUMNS(__Table1,"BS_Year",[Year],"BS_Month",[Month],"BS_Day",[Value],"AD_Date",[__ADDate])
eyJrIjoiMDcwMGJiOTctYzZiMC00ZmQ2LWFmZTEtOGE3NGNkYjY4YjIzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Dear greg I was looking for same. I want to use the dax solution in power pivot, but could not figure out how to use this to create bikram sambat date table. Please kindly support me how can i create bikram sambat date table in power pivot ?