Forum Discussion

WilliamAzevedo's avatar
WilliamAzevedo
Advocate II
1 year ago
Solved

Transform text lines into date columns

Hi.   I have a budget control that must show how much money was budgeted and how much was spent and compare both using a columns and line visual. The problem is my data has no date because the mont...
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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"

SundarRaj's avatar
SundarRaj
Super User
1 year ago

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