Power Query - Current Row & Row Above with conditions

[rsbin]

Good Day,

Need some help in Power Query.  Working with a Financial Statement imported from NetSuite.
Need a NewColumn that meets the following conditions:
if CurrentRow StartsWith "Total - " and [GL_Prefix] from Row Above is not null, 
then NewColumn = [Financial Row] & [GL_Prefix] from Row Above, else NewColumn is null

From my research, I believe I need to add an IndexColumn first.   But then haven't been able to get my conditional syntax working as it should.

Financial Row	GL_Prefix	NewColumn
410110 - Operations - Gate	410
410920 - Sales Discount	410
Total - Operations	null	Total - Operations - 410
Operations	null
510001 - Inter-Terminal Travel	510
510120 - COGS - Operations - Lift	510
510210 - COGS - Equipment Management - Fleet	510
Total - Operations	null	Total - Operations - 510

Appreciate any and all assistance.

Much thanks and Regards,

[ajaybabuinturi · Accepted solution]

Hello @rsbin,
You are on right path, I'm extending a solution that will able to complete your requirement.

1. Create a Duplicate copy of Table 1 (original table) as Table 2
2. Then add index column to the table 1 starting from 0
3. Repeat the point 2 for table 2 starting from 1
4. Then merge table 2 with table 1 based on Index column
5. Expand the "Prev_GL_Prefix" column
6. Add new column using below M-Language

= Table.AddColumn(#"Expanded Table 2", "Custom", each if Text.StartsWith([Financial Row], "Total - ") and [Prev_GL_Prefix] <> null then [Financial Row] & " - " & Text.From([Prev_GL_Prefix]) 
else null)​

• Here are the working table snippet
  Table2
  

  
  Table1 M-Language

  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY67CoNAEEV/ZbFW2JFYpM5DhAQL7cRiCJOwsM6adcz3Z11SaAip5nHP3Lldl+xAA2iVqXokj2IcT2EoUShJFzHp0wjt8wVq0NKkjma6uZllRbRO0G5cgsiztVH9vS1Aaw3hqGIhn7XkB8PBpfX4IhvI4mMeKsT3h7psvqNezF22bA4r9vSczTgQi7oi44Nim6mzJVqf/cnfvwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Financial Row" = _t, GL_Prefix = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Financial Row", type text}, {"GL_Prefix", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Table 2", {"Index"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Prev_GL_Prefix"}, {"Prev_GL_Prefix"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table 2", "Custom", each if Text.StartsWith([Financial Row], "Total - ") and [Prev_GL_Prefix] <> null then [Financial Row] & " - " & Text.From([Prev_GL_Prefix]) 
else null)
in
    #"Added Custom"

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[rsbin]

@mh2587 , @ajaybabuinturi ,
Thank you both for replying.  I think I understand where I was going wrong.
Instead of duplicating my Table, I found another approach that seems to be working for me to find my [GL_Previous].  Then did my IF statement as a separate step as was suggested.

#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"AddedColumn" = Table.AddColumn(#"Added Index", "GL_Previous", each try #"Added Index"[GL_Prefix]{ [Index] - 1 } otherwise null ),
    #"Changed Type1" = Table.TransformColumnTypes(AddedColumn,{{"GL_Prefix", type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"GL_Previous", type text}}),
    #"Added FinancialRow_New" = Table.AddColumn(#"Changed Type2", "FinancialRow_New", each if Text.StartsWith( [Financial Row], "Total -")
and [GL_Previous] <> null 
then [Financial Row] & " - " & [GL_Previous]
else ""),

  Thanks again and Kindest Regards to you both 

[mh2587]

• Add an Index Column starting from 0.
• Duplicate the query and name the duplicate PreviousRowGLPrefix.
• In the duplicated query, keep only the Index and GL_Prefix columns.
• Rename GL_Prefix to GL_Prefix_Above.
• Increment the Index column by 1 to shift it upward.
• Go back to the original query.
• Merge the original query with PreviousRowGLPrefix on the Index column using a Left Outer Join.
• Expand the GL_Prefix_Above column from the merged table.
• Add a custom column with this formula:
  if Text.StartsWith([Financial Row], "Total - ") and [GL_Prefix_Above] <> null then [Financial Row] & " - " & Text.From([GL_Prefix_Above]) else null
• Name the new column NewColumn.
• Remove the Index and GL_Prefix_Above columns if no longer needed.