Forum Discussion
Split data into monthly budget figure
Anonymous
Sure thing!
When you've splitted the columns, add prefix indicating the month. E.g. the first column represents Feb 2018 so add a prefix "Feb", similarly the last column (after you've deleted the 0 value column) is Jan 2019 so adda prefix "Jan" --> this can be done through the transform tab and format - add prefix feature.
After that select all the month columns and in the Transform tab click Unpivot Columns. Then, from the Add Column tab, add a custom column with this formula
if Text.Contains([Value],"Jan") then [year column name]+1 else [year column name]
Then, on the Add Column tab, click Extract - text before delimiter and choose space as the delimiter. Then switch back to the Transform tab click Extract - text after delimiter and choose space as the delimiter.
Here is the outcome:
You can also open the advanced editor from the Home tab in Query Editor and copy my code - you just have to make sure your column a named the same way as mine are! Here's my code after splitting and deleting the extra column:
#"Added Prefix" = Table.TransformColumns(#"Removed Columns", {{"gd-budget.1", each "February " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix1" = Table.TransformColumns(#"Added Prefix", {{"gd-budget.2", each "March " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix2" = Table.TransformColumns(#"Added Prefix1", {{"gd-budget.3", each "Apr " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix4" = Table.TransformColumns(#"Added Prefix2", {{"gd-budget.4", each "May " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix5" = Table.TransformColumns(#"Added Prefix4", {{"gd-budget.5", each "June " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix6" = Table.TransformColumns(#"Added Prefix5", {{"gd-budget.6", each "July " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix7" = Table.TransformColumns(#"Added Prefix6", {{"gd-budget.7", each "August " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix8" = Table.TransformColumns(#"Added Prefix7", {{"gd-budget.8", each "September " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix9" = Table.TransformColumns(#"Added Prefix8", {{"gd-budget.9", each "October " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix10" = Table.TransformColumns(#"Added Prefix9", {{"gd-budget.10", each "November " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix11" = Table.TransformColumns(#"Added Prefix10", {{"gd-budget.11", each "December " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix3" = Table.TransformColumns(#"Added Prefix11", {{"gd-budget.12", each "January " & Text.From(_, "fi-FI"), type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Prefix3", {"gaid", "gd-year"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Correct Year", each if Text.Contains([Value],"Jan") then [#"gd-year"]+1 else [#"gd-year"]),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Added Custom", "Correct Month", each Text.BeforeDelimiter([Value], " "), type text),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Inserted Text Before Delimiter", {{"Value", each Text.AfterDelimiter(_, " "), type text}})
in
#"Extracted Text After Delimiter"If you have trouble, send a picture of your table in the query editor and your advanced editor code!
Br,
T
Anonymous thanks again. I tried you code and had to change the column from gd to gb but I am getting an error as below
Here is the code I am using
let
Source = Odbc.DataSource("dsn=moveware", [HierarchicalNavigation=true]),
MOVEWARE_Database = Source{[Name="MOVEWARE",Kind="Database"]}[Data],
PUB_Schema = MOVEWARE_Database{[Name="PUB",Kind="Schema"]}[Data],
gbal_Table = PUB_Schema{[Name="gbal",Kind="Table"]}[Data],
#"Split Column by Delimiter" = Table.SplitColumn(gbal_Table, "gb-budget", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"gb-budget.1", "gb-budget.2", "gb-budget.3", "gb-budget.4", "gb-budget.5", "gb-budget.6", "gb-budget.7", "gb-budget.8", "gb-budget.9", "gb-budget.10", "gb-budget.11", "gb-budget.12", "gb-budget.13"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"gb-budget.1", Int64.Type}, {"gb-budget.2", Int64.Type}, {"gb-budget.3", Int64.Type}, {"gb-budget.4", Int64.Type}, {"gb-budget.5", Int64.Type}, {"gb-budget.6", Int64.Type}, {"gb-budget.7", Int64.Type}, {"gb-budget.8", Int64.Type}, {"gb-budget.9", Int64.Type}, {"gb-budget.10", Int64.Type}, {"gb-budget.11", Int64.Type}, {"gb-budget.12", Int64.Type}, {"gb-budget.13", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"gb-budget.13"})
in
#"Removed Columns"
#"Added Prefix" = Table.TransformColumns(#"Removed Columns", {{"gb-budget.1", each "February " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix1" = Table.TransformColumns(#"Added Prefix", {{"gb-budget.2", each "March " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix2" = Table.TransformColumns(#"Added Prefix1", {{"gb-budget.3", each "Apr " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix4" = Table.TransformColumns(#"Added Prefix2", {{"gb-budget.4", each "May " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix5" = Table.TransformColumns(#"Added Prefix4", {{"gb-budget.5", each "June " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix6" = Table.TransformColumns(#"Added Prefix5", {{"gb-budget.6", each "July " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix7" = Table.TransformColumns(#"Added Prefix6", {{"gb-budget.7", each "August " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix8" = Table.TransformColumns(#"Added Prefix7", {{"gb-budget.8", each "September " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix9" = Table.TransformColumns(#"Added Prefix8", {{"gb-budget.9", each "October " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix10" = Table.TransformColumns(#"Added Prefix9", {{"gb-budget.10", each "November " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix11" = Table.TransformColumns(#"Added Prefix10", {{"gb-budget.11", each "December " & Text.From(_, "fi-FI"), type text}}),
#"Added Prefix3" = Table.TransformColumns(#"Added Prefix11", {{"gb-budget.12", each "January " & Text.From(_, "fi-FI"), type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Prefix3", {"gaid", "gb-year"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Correct Year", each if Text.Contains([Value],"Jan") then [#"gd-year"]+1 else [#"gd-year"]),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Added Custom", "Correct Month", each Text.BeforeDelimiter([Value], " "), type text),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Inserted Text Before Delimiter", {{"Value", each Text.AfterDelimiter(_, " "), type text}})
in
#"Extracted Text After Delimiter"
Anonymous
Try this:
(forgot to mention in my previous reply that the code should en in an "in" statement and you have to delete some of your code before copying mine).
let Source = Odbc.DataSource("dsn=moveware", [HierarchicalNavigation=true]), MOVEWARE_Database = Source{[Name="MOVEWARE",Kind="Database"]}[Data], PUB_Schema = MOVEWARE_Database{[Name="PUB",Kind="Schema"]}[Data], gbal_Table = PUB_Schema{[Name="gbal",Kind="Table"]}[Data], #"Split Column by Delimiter" = Table.SplitColumn(gbal_Table, "gb-budget", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"gb-budget.1", "gb-budget.2", "gb-budget.3", "gb-budget.4", "gb-budget.5", "gb-budget.6", "gb-budget.7", "gb-budget.8", "gb-budget.9", "gb-budget.10", "gb-budget.11", "gb-budget.12", "gb-budget.13"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"gb-budget.1", Int64.Type}, {"gb-budget.2", Int64.Type}, {"gb-budget.3", Int64.Type}, {"gb-budget.4", Int64.Type}, {"gb-budget.5", Int64.Type}, {"gb-budget.6", Int64.Type}, {"gb-budget.7", Int64.Type}, {"gb-budget.8", Int64.Type}, {"gb-budget.9", Int64.Type}, {"gb-budget.10", Int64.Type}, {"gb-budget.11", Int64.Type}, {"gb-budget.12", Int64.Type}, {"gb-budget.13", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"gb-budget.13"}), #"Added Prefix" = Table.TransformColumns(#"Removed Columns", {{"gb-budget.1", each "February " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix1" = Table.TransformColumns(#"Added Prefix", {{"gb-budget.2", each "March " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix2" = Table.TransformColumns(#"Added Prefix1", {{"gb-budget.3", each "Apr " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix4" = Table.TransformColumns(#"Added Prefix2", {{"gb-budget.4", each "May " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix5" = Table.TransformColumns(#"Added Prefix4", {{"gb-budget.5", each "June " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix6" = Table.TransformColumns(#"Added Prefix5", {{"gb-budget.6", each "July " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix7" = Table.TransformColumns(#"Added Prefix6", {{"gb-budget.7", each "August " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix8" = Table.TransformColumns(#"Added Prefix7", {{"gb-budget.8", each "September " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix9" = Table.TransformColumns(#"Added Prefix8", {{"gb-budget.9", each "October " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix10" = Table.TransformColumns(#"Added Prefix9", {{"gb-budget.10", each "November " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix11" = Table.TransformColumns(#"Added Prefix10", {{"gb-budget.11", each "December " & Text.From(_, "fi-FI"), type text}}), #"Added Prefix3" = Table.TransformColumns(#"Added Prefix11", {{"gb-budget.12", each "January " & Text.From(_, "fi-FI"), type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Prefix3", {"gaid", "gb-year"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Correct Year", each if Text.Contains([Value],"Jan") then [#"gd-year"]+1 else [#"gd-year"]), #"Inserted Text Before Delimiter" = Table.AddColumn(#"Added Custom", "Correct Month", each Text.BeforeDelimiter([Value], " "), type text), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Inserted Text Before Delimiter", {{"Value", each Text.AfterDelimiter(_, " "), type text}}) in #"Extracted Text After DelimiterAh ok, sorry hadn't realise I deleted some code. did get a new error with yours though. when I clicked show error irt highlight the bottom line
Anonymous
You're missing a " at the end :)