Forum Discussion
Transform text lines into date columns
Please try this WilliamAzevedo
What I think the reason could be is, that your data does not seem to have strictly two rows for a particular project number and month i.e. The Budget and The Spent. Apart from these two, there are certain rows for a particular project number and month which are null as well that get picked up while expanding when just "Column1" and "Column2" are asked to be picked up by the code. I'll leave the changed code where I have tried remove any null values from the list after grouping ( List.RemoveNulls(_[Value]) ) which eventually expand just the rows with numbers. Thanks!let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", 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 _[Value]}}),
Custom2 = Table.TransformColumns(#"Grouped Rows",{"All", each List.RemoveNulls(_)}),
#"Extracted Values" = Table.TransformColumns(Custom2, {"All", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "All", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"All.1", "All.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"All.1", type text}, {"All.2", type text}})
in
#"Changed Type1"
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!
I don't know what I did wrong, but the result was the unpivoted columns returning to pivoted.
Hi WilliamAzevedo, if you don't know how to use my query - read note below my post.