Split data into monthly budget figure

[Anonymous]

Hello, I have a table with a monthly budget figure in gbal,(gb-budget) that has that monthly budget amount and then a semi-colon and then the next month ETC as below. To add complication this is our financial year so in the gb-budget column for 2018 below the 1st figure month is February 2018 and the last is January 2019. I need to also drop the last 0 as well.

gaid	gb-budget	gb-year
560	350000;351500;353000;354000;355500;357000;358500;359500;361000;362500;364000;365500;0	2018
560	0;0;0;0;0;0;0;0;0;0;0;0;0	2017

I have the actuals in another table so I want to display the budget figure month and year against the actual figure month and year. Is there a way of putting this into a table like this?

The actuals table is made up of a combination of what we have invoiced. the table is custinvd in a column called Net Value

sorry hopefully i have explained this correctly

thanks

[Anonymous]

Hi @Anonymous,

If I understood correctly, you want the values as separate columns.

In the Query Editor you can use the Split Column feature - this create separate columns based on a delimiter - in this case a semicolon. To drop the last 0 just delete the 13th column created by the split.

To get the years and months correctly you could add a month prefix to each column, unpivot the columns, create a custom column with the correct month-year combination and then extract the prefixes. (I cut some corners, but the principle is the same)

Hope this helps.

Br,

T

[Anonymous]

Hi @Anonymous thanks for your help, the first bit I did and get where you are going but the 2nd bit I can't get my head around (sorry my power BI knowledge is not that great).

could you spell it out a little bit for me please (even put the formulas on here)

thanks

[Anonymous]

@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]

@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]

@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 Delimiter

[Anonymous]

Ah 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]

@Anonymous

You're missing a " at the end 🙂