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"
your data does not seem to have strictly two rows for a particular project number and month
You are correct, it does not have. The reason is that a project may or may not have it's values readjusted along time (thus some of them have). Sould I try it anyway?
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!
It worked, thank you very much!
Also, thank you, dufoq3, for showing another possibility!