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 , 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.
When you grouped the column and recieved the table in the column "Todos". The column that I extracted "Value", is the amount column name "Value" as well? If not, then change the _[ColumnName] that is _[Valor]. Post this, you can simply follow the next steps in the code. Let me know if this works. Thanks
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