Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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 Delimiter
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
You're missing a " at the end 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |