How to sort or configure data attributes related to Fiscal dates

[mario_ruiz]

I have these fiscal date attributes set as String type

And I don't know how to set up them to get the value of the Date table I set. The Date table is simply like this:

 I think I do need to extract the month and year from those attributes and convert them into dates to be able to visualize them correctly, is that a correct approach? What else should I do from this data to a more valuable insights?

[PijushRoy]

Hi @mario_ruiz 

Please copy the code and paste in Advance Editor and check
I considered Fiscal Year start from April to March

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZY9a1VRFET/yiO1RWbO/SwFK0EtLIOFhocKWxNiIux/7zXdnZnyrGotZnPfu7u7ucXlw9en+x8X3nLcfHlzEBoZRiYjs5HFyGpkM7Irwa0Rc4Y5w5xhzjBnmDPMGeYMc6Y505xpzjRnmjPNmeZMc6Y505yHOQ+/DVzePj79rNO1KBlGJiOzkcXIamQzsis5rkWJOcOcYc4wZ5gzzBnmDHOGOdOcac40Z5ozzZnmTHOmOdOcac7DnF+/JC3fkT7dxfk9yXuW9yLvVd6bvPfz+/W70ac7OL/FD+IH8YP4QfwgfhA/iB/Fj+JH8aP4UfwofhQ/ih/Fj+I3xG/ovri8f/l9PQ0uYCiYFMwKFgWrgk3BLuAYXoCaQk2hplBTqCnUFGoKNYWaUk2pplRTqinVlGpKNaWaUk2ppkNNX9evlvWrZf1qWb9a1q+W9atl/WpZv1rWr5b1q2X9alm/WtavlvWrZf1qWb9a1q+W9atl/WpZv1rWr5b1q2X9alm/WtavlvWrZf1qWb9a1q+W9ev8LZAZjh+sl+8vf57P/wwUDUeTo9nR4mh1tDnaDf3/h6DI7eH2cHu4Pdwebg+3h9vD7en2dHu6Pd2ebk+3p9vT7en2dPvh9iNcDi6fr4/P11/frk+ne0p0RDpFOke6RLpGukW6J3rcWaKxDbENsQ2xDbENsQ2xDbENsY2xjbGNsY2xjbGNsY2xjbGNsY2xbcS24yY/3T8/6EU6G4FNgc2BLYGtgW2B7c6OC3QWOhA6EDoQOhA6EDoQOhA6EDoYOhg6GDoYOhg6GDoYOhg6GDoYOkboGOmucPn48Nc/fwGOBKcE5wSXBNcEtwT3AI+jCzAVIRUhFSEVIRUhFSEVIRUhFTEVMRUxFTEVMRUxFTEVMRUxFTEVjVR0XN27671fXYAjwSnBOcElwTXBLcE9wOPqAkxFSEVIRUhFSEVIRUhFSEVIRUxFTEVMRUxFTEVMRUxFTEVMRUxFIxUNK/ryDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Year", "Fiscal Year", each if [Month]>3 then [Year] else [Year]-1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Fiscal Year", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "Fiscal Year (Final)", each "FY"&Text.End([Fiscal Year],2)),
    #"Inserted Quarter" = Table.AddColumn(#"Added Custom3", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Quarter",{"Quarter"}),
    #"Inserted Quarter1" = Table.AddColumn(#"Removed Columns", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Inserted Quarter1", "Month2Digit", each "M"&Number.ToText([Month],"D2")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Quarter2Digit", each "Q"&Number.ToText([Quarter],"D2")),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Final Output", each [#"Fiscal Year (Final)"]&[Quarter2Digit]&[Month2Digit])
in
    #"Added Custom4"

If solve, mark as SOLUTION