Forum Discussion

rsbin's avatar
rsbin
Community Champion
1 year ago
Solved

Power Query - Current Row & Row Above with conditions

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 ...
Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    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.

ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

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's avatar
rsbin
Community Champion
1 year ago

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