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_DateHi @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!
Since the data I posted was just an example, I'm having some difficulty on replicating the process to my actual data.
Until here:
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
Custom1 = Table.TransformColumns(#"Unpivoted Other Columns", {"Attribute", each Text.End(_,3)}),I have this result:
But from here I couldn't figure how to make the "Spent" lines become a column. If I try this:
Here's the result:
And @lbendlin, I appreciate very much your help! I just need the "Spent" field to be a column in my table, so I can use it as a field in the graph and line visual instead of a matrix.
It did not. And this time I tried to make eve the column names as similar to your instructions as possible:
The columns were created, but with values null (here I'm displaying the last rows, but al of them are null😞
@WilliamAzevedo , it seems the problem is at the Grouped Rows step. I hope you are getting a table which contains three columns i.e. "Invest", "Attributo" and "Valor" similar to the first image below.
In case after grouping you have the following table format, you'd only require the column which contains the number - _[ValueColumnName]
Please check if the grouped table is in the format below
Also do share the image of the error or the step of the "Table" of the grouped rows step
It does result a Table column, but with four columns:
The view of a selected row:
The result when I expand the column using the expand button:
Result when I expand typing the code:
The code I typed:
hI @WilliamAzevedo , Sorry for the late response. Yes please try out the above code and see if it works as per your requirements. Thanks
Only if there are null values, it would work, but if those extra columns aren't null. What you can do is, while expanding don't use the code but do it manually so it automatically expands into X number of columns in your data.
Instead of using the code which has just two columns, just expand it through the icon above, it will automatically expand into the number of columns that you have. Thanks!
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
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
