Forum Discussion
DAX - Need next row value
Hello -
I am attempting to create a new column utilizing DAX (or taking advice on how to accomplish the following). I have a dataset that sometimes includes 'Beginning Balance' for [Date] and Null for [Account]. I am trying to replace the Null with the next rows value (within the same column). Please reference the attached picture for current data set on the left and my future goal on the right.
Thank you,
Justice
JusticeBaird this is not an analysis level task, rather it is a data level task which can be resolved by using PQ in the following way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpNV3BKzFHSUYIgQwMDA6VYnWglIwMjQ10DIDIBijol5mUDKV2gLJCyhCpB12uKotcQpB0o6qgfANJqCtZqAqJiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Amount = _t, Balance = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Account", type text}, {"Amount", Int64.Type}, {"Balance", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "newAccount", each let x = #"Added Index"[Account], y = #"Added Index"[Date], z = if [Date]="Beg Bal" then x{[Index]+1} else [Account] in z), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"
7 Replies
- BeaBF
Super User
Sample data is stated below BeaBF . Please note that my only goal is to bring in the applicable account name for for the blank 'Beginning Balance' rows. Please let me know if you have any further questions.
Date | Account | Amount | Balance
Beg Bal. | | | 1000
1/4/2021 | Bank | -100 | 900
Beg Bal. | | | 5000
1/11/21 | A/P | -500 | 4500
- BeaBF
yes, but if the lines were not in this order, you need a rule to assign the occunt bank rather than loan. what is this rule?
smpa01Community Champion
JusticeBaird this is not an analysis level task, rather it is a data level task which can be resolved by using PQ in the following way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpNV3BKzFHSUYIgQwMDA6VYnWglIwMjQ10DIDIBijol5mUDKV2gLJCyhCpB12uKotcQpB0o6qgfANJqCtZqAqJiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Amount = _t, Balance = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Account", type text}, {"Amount", Int64.Type}, {"Balance", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "newAccount", each let x = #"Added Index"[Account], y = #"Added Index"[Date], z = if [Date]="Beg Bal" then x{[Index]+1} else [Account] in z), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}) in #"Removed Columns"I copy/pasted you logic and came up with the same results as you, now which part of the logic to I need to update to return the full table?
Thank you,
