Creating Fiscal Year Date for Cost Fields

[jrdn03]

I'm not sure how to do this... And yes, the primary reason this is an issue is because the data elements were not defined like a database.

I have multiple cost fields for each Fiscal Year, each representing a "Cost type". The field name themselves denote the Fiscal Year.
e.g. "Salary Cost 2023-24".   

I am trying to create visuals that organize these cost fields by their Fiscal Year. However, I don't have a true date field to utilize because the date is assumed in the cost field title. What is the best way to create date fields in order to categorize the costs by Year? 
If a project has Salary Cost for 2023-24 and Salary Cost for 2024-25 and Capital Cost for 2024-25 then we want to see these broken by year. For example, a stacked bar chart with Fiscal Year at the bottom and the different cost types as bars. 

please note that there are multiple queries and the relationships also need to be maintainted. 

Thanks in advance!

[jgeddes · Accepted solution]

If you have data somewhat like...

and you want...

you can use Power Query to unpivot the cost fields with the dates in the titles, then split the column with the names so the cost type is seperated from the year. From there you can pivot the cost type column without aggregating the rows to get back to the original layout. 
The following code shows the steps (you can paste it into the Advanced Editor of a blank query)

let
//manually created data table
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYy5EQAxCAN7IXZi8/gc3tMFQ/9tGHROFkarkTvd1KgPTqokRGfSJkVzesqxaCWw/T96/Js/r9IYWJehKkd/pUfHKmCMam5EbA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Salary Cost 2023-24" = _t, #"Capital Cost 2023-24" = _t, #"Salary Cost 2024-25" = _t, #"Capital Cost 2024-25" = _t]),
//change data types
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Salary Cost 2023-24", Int64.Type}, {"Capital Cost 2023-24", Int64.Type}, {"Salary Cost 2024-25", Int64.Type}, {"Capital Cost 2024-25", Int64.Type}}),
//unpivot other columns, select all of the columns that are not cost fields and then choose unpivot other columns
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
//split the cost fields into 2 columns by the space delimiter beween 'cost' and the 'year' portions of the column title
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Cost Type", "Fiscal Year"}),
//select the 'cost type' column and select pivot columns making sure to select do not aggregate.
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[#"Cost Type"]), "Cost Type", "Value")
in
#"Pivoted Column"

The actual solution to your issue will depend on the exact format/titles of your columns but hopefully this gets you going in the right direction. 

[jrdn03]

Thanks for the reply. I was able to transform as you suggested. It looks how I hoped it would. However, I don't think the values are rendering correctly. For example, in the image below, I am getting an $800k value but in Power Query the amount shown for the 2022-23 Total is around $400k. Oddly enough, the other values (bars) show the correct value for their respective years. 

Would this have to do with Pivoting and choosing not to aggregate? I'm not too familiar with this. 
Thanks,

[jgeddes]

Not aggregating when pivoting ensures that all rows are returned in the pivot result. 
If the data that is displaying in Power Query is 'correct' but it is not displaying correctly in the visuals, that would typically mean there is an issue of context filters. If you wanted to share some sample data, with any confidential info redacted, I might be able to help.