Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Split data into monthly budget figure

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.

 

gaidgb-budgetgb-year
560350000;351500;353000;354000;355500;357000;358500;359500;361000;362500;364000;365500;02018
5600;0;0;0;0;0;0;0;0;0;0;0;02017

 

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

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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