Forum Discussion
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 [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,
Hello rsbin,
You are on right path, I'm extending a solution that will able to complete your requirement.- Create a Duplicate copy of Table 1 (original table) as Table 2
- Then add index column to the table 1 starting from 0
- Repeat the point 2 for table 2 starting from 1
- Then merge table 2 with table 1 based on Index column
- Expand the "Prev_GL_Prefix" column
- 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.
3 Replies
- 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.
Hello rsbin,
You are on right path, I'm extending a solution that will able to complete your requirement.- Create a Duplicate copy of Table 1 (original table) as Table 2
- Then add index column to the table 1 starting from 0
- Repeat the point 2 for table 2 starting from 1
- Then merge table 2 with table 1 based on Index column
- Expand the "Prev_GL_Prefix" column
- 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.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
