Transform text lines into date columns

Your source data is inconsistent. Sometimes there is a space after "Spent" and sometimes there is none.

After unpivoting and cleaning your data you can load it into Power BI

Hi WilliamAzevedo , here's a solution you could look at. I'll leave the code used below. Thanks!

let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Budget Jan", type text}, {"Spent Jan", type text}, {"Budget Fev", type text}, {"Spent Fev", type text}, {"Budget Mar", type text}, {"Spent Mar", type text}, {"Budget Abr", type text}, {"SpentAbr", Int64.Type}, {"Budget Mai", type text}, {"SpentMai", Int64.Type}, {"Budget Jun", type text}, {"SpentJun", Int64.Type}, {"Budget Jul", type text}, {"SpentJul", Int64.Type}, {"Budget Ago", type text}, {"SpentAgo", Int64.Type}, {"Budget Set", type text}, {"SpentSet", Int64.Type}, {"Budget Out", type text}, {"SpentOut", Int64.Type}, {"Budget Nov", type text}, {"SpentNov", Int64.Type}, {"Budget Dez", type text}, {"SpentDez", Int64.Type}}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
Custom1 = Table.TransformColumns(#"Unpivoted Other Columns", {"Attribute", each Text.End(_,3)}),

#"Grouped Rows" = Table.Group(Custom1, {"Project", "Attribute"}, {{"All", each Table.FromRows({_[Value]})}}),

#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column1", "Column2"}, {"Column1", "Column2"}),

Custom2 = Table.RenameColumns(#"Expanded All", List.Zip({Table.ColumnNames(#"Expanded All"),{"Project","Month","Budget","Spent"}}))

in
Custom2

Hi WilliamAzevedo, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZJZbsMwDETv4u8BIS7ichYj979GZaeNZSQwUBhovzR6oihSw3VdeMHCQt0UKkMPKQ7LDRNzh/svaUNrH4mQKEP1PokssF8RJTVH1Q95YF229jQomqDyCH3laD7k/E4wwt8C/3bZCte3IyXOROn+z1IGl4PullJyPa2Z6Pn2RJw0Ail3SVCFQPKKFHV3hM4d2gc7hOVpIFOrjsyDbjLZIX5m57sTMTLpqH6XdOoqMLsiTq7f3rz663tksJKPstP2LVed502GaQqZcomRu8Hiv6fw8QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Budget Jan" = _t, #"Spent Jan" = _t, #"Budget Fev" = _t, #"Spent Fev" = _t, #"Budget Mar" = _t, #"Spent Mar" = _t, #"Budget Abr" = _t, SpentAbr = _t, #"Budget Mai" = _t, SpentMai = _t, #"Budget Jun" = _t, SpentJun = _t, #"Budget Jul" = _t, SpentJul = _t, #"Budget Ago" = _t, SpentAgo = _t, #"Budget Set" = _t, SpentSet = _t, #"Budget Out" = _t, SpentOut = _t, #"Budget Nov" = _t, SpentNov = _t, #"Budget Dez" = _t, SpentDez = _t]),
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Project"}, "Attribute", "Value"),
    ReplacedValue = Table.ReplaceValue(Unpivoted," ","",Replacer.ReplaceText,{"Attribute"}),
    SplitColumn = Table.SplitColumn(ReplacedValue, "Attribute", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Attribute", "Month Name"}),
    Pivoted = Table.Pivot(SplitColumn, List.Distinct(SplitColumn[Attribute]), "Attribute", "Value"),
    Ad_Date = Table.AddColumn(Pivoted, "Date", each Date.FromText("2025" & [Month Name] & "01", [Format="yyyyMMMdd", Culture="pt-PT"]), type date)
in
    Ad_Date

Hi WilliamAzevedo 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Hello everyone and I'm sorry it took me so long to reply. I was out of the office those days and because of that I had neither worked on the task I asked help for, nor accessed the community.

So, today I had to make a change a in my model (SharePoint folder with .txt files as source instead of .xlsx files) and for that reason I think I have to ask: do the methods still apply? While this message reaches you, I will try it anyway.