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"
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: