Forum Discussion
Split annual value across 12 months with dates
I have a simple table with an annual Amount. I need to split this so I can connect to a date slicer. The AnualAmount needs to be divided by 12 then that amount in the table with a monthly date.
My Table is currently like this.
Table Name: Annual Sales
I need the total amount split by 12 months like this
Thank in advance😊
Hi again SysJessica1 ,
Whenever the name of a step in power query contains a space, the name needs to be in quotes and preceeded by a "#" character. For example #"Changed Type". If this step were named ChangedType, without a space, it woudn't need the "#" or quote marks.
When you create the blank query, open it in the Advanced Editor (Power Query Home Ribbon -> Advanced Editor button). You will see
let
Source = ""
in
SourceReplace all of this with the code in my previous reply and click "Done". This will yield the result in the image I included in my previous reply. The first two steps in the code - "Source" and "Changed Type" are only there to recreate a sample of data - it's the steps after that which matter.
However, that is only for demo purposes. To use my code in your own query do the following.
- Open your query in Advanced Editor.
- Delete the last two lines of your query (the "in" line and whatever follows it e.g....)
in
#"Changed Type1" - Add a comma at the end of the last line you have left.
- After the comma place my code (excluding its first two lines).
- Where I have #"Create lists of monthly amount and date" replace that with the name of the last step you had after you deleted your last two lines.
- Click "Done".
This will create a monthly cost for each month of 2024 for all your IDNumbers.
Hope this works for you.
4 Replies
collinsgSolution Sage
Good day SysJessica1 ,
Here is an option.
- Transform "AnnualAmount" into a list of lists - with each list containing the monthly amount and start of month (do this by creating, for each AnnualAmount, a list of 12 numbers, each 1/12 of annual amount and a list of 12 dates for the year - and zip these two lists together).
- Expand the list of lists into a row for each sub-list.
- Expand the sub-lists into text.
- Finish by splitting the text into the amount and date and typing.
This is probably easier to follow by clicking through the steps in the code...here is sample code which you can paste into a blank query and then step through.
Hope this helps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQxMTI1U9JRMjI3MDAA0u5Fqal5Ck6pOTlKsTrRSm4WluamJkBxEwMLsLx/UWJeeqqCR2piUYlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, AnnualAmount = _t, Description = _t]),
#"Changed Type" = Table.TransformColumnTypes(
Source, {{"Number", type text}, {"AnnualAmount", Int64.Type}, {"Description", type text}}),
#"Create lists of monthly amount and date" = Table.TransformColumns(
#"Changed Type",{{"AnnualAmount",
each
List.Zip({
List.Numbers(_ / 12, 12, 0),
List.Transform({1..12}, each #date(2024, _, 1))
})}}),
#"Expand the lists" = Table.ExpandListColumn(#"Create lists of monthly amount and date", "AnnualAmount"),
#"Extracted Values" = Table.TransformColumns(
#"Expand the lists", {"AnnualAmount", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
#"Split amount from date" = Table.SplitColumn(
#"Extracted Values",
"AnnualAmount",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Monthly Amount", "Date"} ),
#"Changed Type1" = Table.TransformColumnTypes(#"Split amount from date",{{"Monthly Amount", Int64.Type}, {"Date", type date}})
in
#"Changed Type1"which gives...
I tried to put this into a blank query and maybe I am doing this completely wrong as I have never done a query before. But it just gives me the code then I try to convert it to a table and it is blank
I am not sure what those # is?
Is the intent for me to create multiple tables using your code?
- collinsg
Hi again SysJessica1 ,
Whenever the name of a step in power query contains a space, the name needs to be in quotes and preceeded by a "#" character. For example #"Changed Type". If this step were named ChangedType, without a space, it woudn't need the "#" or quote marks.
When you create the blank query, open it in the Advanced Editor (Power Query Home Ribbon -> Advanced Editor button). You will see
let
Source = ""
in
SourceReplace all of this with the code in my previous reply and click "Done". This will yield the result in the image I included in my previous reply. The first two steps in the code - "Source" and "Changed Type" are only there to recreate a sample of data - it's the steps after that which matter.
However, that is only for demo purposes. To use my code in your own query do the following.
- Open your query in Advanced Editor.
- Delete the last two lines of your query (the "in" line and whatever follows it e.g....)
in
#"Changed Type1" - Add a comma at the end of the last line you have left.
- After the comma place my code (excluding its first two lines).
- Where I have #"Create lists of monthly amount and date" replace that with the name of the last step you had after you deleted your last two lines.
- Click "Done".
This will create a monthly cost for each month of 2024 for all your IDNumbers.
Hope this works for you.
This worked! Thank you for your help!
