Forum Discussion
Adding Custom Column To Obtain Prior Month Balance
Trying to create a custom column 'Prior Month Balance' by looking at the [Balance] column of the prior month with the same Entity and Account Number.
Here are the relevant columns of the table:
Entity Type Text
Account Number Type Text
Date Type Date
Balance Type Fixed Decimal
Managed to create a Measure, but want to have this in the table from the outset.
I'll spare you the details of various attempts with circular references, etc..., even spent a day with ChatGPT to no avail. :).
Thank you for any insight you may provide. Jim
JRParker my bad. I wanted to sort by date upon grouping but then changed my mind... Before I give up and commit a suicide, lets replace function f with the following
f = (tbl as table) as table => [sorted = Table.Sort(tbl, "Date"), // Sort the table by the "Date" column prior_month = {0} & List.RemoveLastN(sorted[Balance], 1), // Create a list of prior month balances by removing the last balance value and appending a 0 at the beginning out = Table.FromColumns(Table.ToColumns(sorted) & {prior_month}, Table.ColumnNames(sorted) & {"Prior Month"}) // Add the prior month balances as a new column named "Prior Month" ] [out]
37 Replies
JRParker See if this helps, this video covers all of the common time intelligence scenarios.
The PBIX is available here: MicrosoftHatesGregsQuickMeasures/PBIX at main · gdeckler/MicrosoftHatesGregsQuickMeasures (github.com)
Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Thanks Greg... replied to my original post, but will reply directly to your post.
....the intent is create the custom column in Power Query; not Data View. Here is sample data; all but the last column is sample data from the table, with the last column the custom column with the expected results:
Entity Account Number Date Balance Prior Month FVE 4000 3/31/2022 ($2,330.08) $0.00 FVE 4000 4/30/2022 ($4,890.36) ($2,330.08) FVE 4000 5/31/2022 ($5,972.62) ($4,890.36) FVE 4000 6/30/2022 ($13,285.10) ($5,972.62) FVE 4000 7/31/2022 ($14,388.49) ($13,285.10) FVE 4000 8/31/2022 ($20,713.00) ($14,388.49) FVE 4000 9/30/2022 ($32,338.88) ($20,713.00) FVE 4000 10/31/2022 ($31,558.75) ($32,338.88) FVE 4000 11/30/2022 ($47,740.27) ($31,558.75) FVE 4000 12/31/2022 ($49,812.30) ($47,740.27) FVE 4000 1/31/2023 ($2,910.00) ($49,812.30) FVE 4000 2/28/2023 ($15,823.60) ($2,910.00) FVE 4000 3/31/2023 ($25,206.91) ($15,823.60) FVE 4000 4/30/2023 ($35,934.17) ($25,206.91) FVE 4000 5/31/2023 ($57,606.09) ($35,934.17)
BTW, the intent is create the custom column in Power Query; not Data View. Here is sample data; all but the last column is sample data from the table, with the last column the custom column with the expected results:
Entity Account Number Date Balance Prior Month FVE 4000 3/31/2022 ($2,330.08) $0.00 FVE 4000 4/30/2022 ($4,890.36) ($2,330.08) FVE 4000 5/31/2022 ($5,972.62) ($4,890.36) FVE 4000 6/30/2022 ($13,285.10) ($5,972.62) FVE 4000 7/31/2022 ($14,388.49) ($13,285.10) FVE 4000 8/31/2022 ($20,713.00) ($14,388.49) FVE 4000 9/30/2022 ($32,338.88) ($20,713.00) FVE 4000 10/31/2022 ($31,558.75) ($32,338.88) FVE 4000 11/30/2022 ($47,740.27) ($31,558.75) FVE 4000 12/31/2022 ($49,812.30) ($47,740.27) FVE 4000 1/31/2023 ($2,910.00) ($49,812.30) FVE 4000 2/28/2023 ($15,823.60) ($2,910.00) FVE 4000 3/31/2023 ($25,206.91) ($15,823.60) FVE 4000 4/30/2023 ($35,934.17) ($25,206.91) FVE 4000 5/31/2023 ($57,606.09) ($35,934.17) Know there are other Entities and Account Numbers in the table. 🙂
JRParker Here is one way of doing it. However, for additional Entities and Account Numbers you may need to modify things a bit but perhaps not.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLJjcMwDEV7MXIYAgTNRQtVQKaEuQTuv40oiDGxSOQkCfB7X/7i47H9/t033Aozz8V2k11Zde5/bopmTOwwT7e54e3AAJTd+AMU9MFkDQKesLrkVBxdqSkEScLakiaG6pWEIVgS15c4KWjuVAZETQJ97YOxi80eIHoSOJab2qsKJ3eIngQKL5EmWKtTrxBFmZT1LTr2wqQdoiiTumSWgS5Kdv7mRZTJE7TzwYfwfz0XTeJ0V/9wUtHVqDEETeJszauo3GgIRM3XOX2DNmfFCsnZzcXzdVLfYO3Y5pd8js7FcxxP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Account Number" = _t, Date = _t, Balance = _t, #"Prior Month" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Account Number", Int64.Type}, {"Date", type date}, {"Balance", Currency.Type}, {"Prior Month", Currency.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Prior Month"}), #"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index.1", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter), #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Balance"}, {"Added Index1.Balance"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,0,Replacer.ReplaceValue,{"Added Index1.Balance"}) in #"Replaced Value"
Oh, and I'm using Power BI Desktop.
JRParker Here is the PBIX file (attached below signature).
Greg, thanks for providing the pbix file; very helpful. While your pbix code is right on with the correct results, I've added what I believe to be the relevant lines to the query of my existing table and have incorrect results. Can you advise what I've done wrong? Note there are two Merged Queries:
let
// note this table is first a reference to a Trial Balance table
Source = #"Trial Balance",
#"Merged Queries" = Table.NestedJoin(Source, {"Account Number"}, #"Account Category", {"Account Number"}, "Account Category", JoinKind.LeftOuter),
#"Expanded Account Category" = Table.ExpandTableColumn(#"Merged Queries", "Account Category", {"Statement"}, {"Account Category.Statement"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Account Category", each ([Account Category.Statement] = "Income Statement")),// this is the point where we have the existing table and want to add the custom column
// added these steps from your code (along with the ',' at the end of the previous line
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Merged Queries-2" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries-2", "Added Index1", {"Balance"}, {"Added Index1.Balance"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Added Index1",null,0,Replacer.ReplaceValue,{"Added Index1.Balance"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Date", Order.Ascending}}),// added these over and above your code to remove unncessary columns and rename the custom column
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Account Category.Statement", "Index", "Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Index1.Balance", "Prior Month Balance"}})
in
#"Renamed Columns"Let me know if you need to know what kind of results other than incorrect results.
Hi Dont want to sidetrack what you are doing but the below video is very similar to what you want to achieve.
Thank you Duh .... very insightful... I have a long way to go in learning the M language. In the interim, I'm not qualified to solve this issue as noted in message 16 in this thread.
