Forum Discussion

JusticeBaird's avatar
JusticeBaird
Frequent Visitor
4 years ago
Solved

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 N...
DAX
Help appreciated
Help me please
Help Requested
  • smpa01's avatar
    smpa01
    4 years ago

    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"

     

     

     

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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"

 

 

 

 

 

JusticeBaird's avatar
JusticeBaird
Frequent Visitor
4 years ago

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,

 

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    JusticeBaird try running the code from #"Added Index" onwards in your original dataset. It should not create any issue.

    • JusticeBaird's avatar
      JusticeBaird
      Frequent Visitor
      4 years ago

      I am still unable to get it to return the information I need.. Would it help if I sent the current Source Logic and see what parts I should update to incorporate your logic?

       

      let
      Source = Excel.Workbook(File.Contents("C:\Users\justi\Desktop\TG\Power BI\Quickbooks\General Ledger.xlsx"), null, false),
      #"General Ledger_sheet" = Source{[Item="General Ledger",Kind="Sheet"]}[Data],
      FilterNullAndWhitespace = each List.Select(_, each _ <> null and (not (_ is text) or Text.Trim(_) <> "")),
      #"Removed Bottom Rows" = Table.RemoveLastN(#"General Ledger_sheet", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
      #"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows", each try List.IsEmpty(List.Skip(FilterNullAndWhitespace(Record.FieldValues(_)), 1)) otherwise false),
      #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Date", type text}, {"Account", type text}, {"Transaction Type", type text}, {"Num", type any}, {"Memo/Description", type text}, {"Split", type text}, {"Amount", type number}, {"Balance", type number}, {"Name", type text}}),
      #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Weird Name"}}),
      #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
      #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Ascending}})
      in
      #"Sorted Rows"