Create Data with New Column dependent on two rows of data

[Linnil]

Hi Everyone

I am working with Power Query.

I have data like this, comes from a TEXT file so I'm trying to normalise it:

Row/Record	Hours Code	Date
1	97	21/02/2024
2	36	22/02/2024
3	99	23/02/2024
4	Approved
5	98	21/02/2024
6	Approved
7	97	23/02/2024
8	Approved

The report prints codes and a date.
If it hits a code which is Approved, the row directly below shows Approved.

Code might be Approved or no message/not approved.
It does not show Rejected for "Not Approved" rows.

I know how to do Fill Up, Fill Down.

Fill UP would do the trick BUT there's no way for me to indicate that a change has occured in the rows which just have consecutive codes. For a Fill UP solution, only the word Approved indicates for the row directly above it that there is a change, so that's not enough.

I would like this:

Row/Record	Hours Code	Approved?
1	97	NO
2	36	NO
3	99	YES
4
5	98	YES
6
7	97	YES
8

If I were doing it in Excel, I do some some String LENGTH comparison (add 2 LENS together, compare to next 2 rows etc) and that would work.

But I'm stumped with this and I've been wrangling data all day - my brain is empty!

Could anyone help me with some tips/ideas?

Thanks very much 🙂

[m_dekorte · Accepted solution]

Hi @Linnil,

You can try something like this. Create a new blank query, and replace everything inside with this code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbI0BxJGhvoGRvpGBkYmSrE60UpGQCFjM5C4EYq4MUi9JUjcGEXcBCjkWFBQlF+WmgJkKoAFTUGKLTANN8Om2BzuElSTLTAUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row/Record" = _t, #"Hours Code" = _t, Date = _t]),
    ShiftHourCode = Table.FromColumns( Table.ToColumns( Source ) & {List.RemoveFirstN( Source[Hours Code] )}, Table.ColumnNames( Source ) & {"t"} ),
    Result = Table.FromRows( List.Transform( Table.ToRows( ShiftHourCode ), 
        (row) => try ( 
            if row{1} = "Approved" 
            then {row{0}, null, null} 
            else if Number.From(row{1}) is number and row{3} = "Approved" 
            then {row{0}, row{1}, "YES"} 
            else {row{0}, row{1}, "NO"} 
        ) otherwise {null, null, null} 
    ), List.FirstN( Table.ColumnNames( Source ), 2) & {"Approved"} )
in
    Result

Returns this result.

I hope this is helpful

[Linnil]

Hi m_dekorte - wow - I didn't know you could do comparisons between rows! And you didn't use the date which is actually better 😉 
Thanks so much and really appreciate your help with this.

[dufoq3 · Accepted solution]

@Linnil, there are many ways:

You can find 2 different versions how to achieve expected result (v1 should be faster, but v2 is easier to understand).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbI0BxJGhvoGRvpGBkYmSrE60UpGQCFjM5C4EYq4MUi9JUjcGEXcBCjkWFBQlF+WmgJkKoAFTUGKLTANN8Om2BzuElSTLVAVK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row/Record" = _t, #"Hours Code" = _t, Date = _t]),
    // Added null row at the beginning
    v1_HelperTable = (#table(Table.ColumnNames(Source), {List.Repeat({null}, List.Count(Table.ColumnNames(Source)))}) & Source),
    v1_Ad_Approved = Table.FromColumns(Table.ToColumns(v1_HelperTable) & Table.ToColumns(Source[[Hours Code]]), Table.ColumnNames(Source) & {"Approved?"}),
    v1_ReplaceApproved = Table.ReplaceValue(v1_Ad_Approved,
     each [#"Approved?"],
     each Text.Trim([Date]),
     (x,y,z)=> if (z = null or Text.Trim(z) = "") then null else if x = "Approved" then "YES" else "NO",
     {"Approved?"} ),
    #"v1_Filtered Rows" = Table.SelectRows(v1_ReplaceApproved, each ([Date] <> null)),
    StepBackToSource = Source,
    v2_AddedIndex1 = Table.AddIndexColumn(StepBackToSource, "Index1", 1, 1, Int64.Type),
    #"v2_Added Index0" = Table.AddIndexColumn(v2_AddedIndex1, "Index0", 0, 1, Int64.Type),
    v2_MergedQueryItself = Table.NestedJoin(#"v2_Added Index0", {"Index1"}, #"v2_Added Index0", {"Index0"}, "Added Index0", JoinKind.LeftOuter),
    v2_ExpandedAddedIndex1 = Table.ExpandTableColumn(v2_MergedQueryItself, "Added Index0", {"Hours Code"}, {"Hours Code.1"}),
    v2_Ad_Approved = Table.AddColumn(v2_ExpandedAddedIndex1, "Approved?", each if [Hours Code.1] = "Approved" then "YES" else  
if [Date] = null or Text.Trim([Date]) = "" then null 
else "NO", type text),
    v2_RemovedColumns = Table.RemoveColumns(v2_Ad_Approved,{"Index1", "Index0", "Hours Code.1"})
in
    v2_RemovedColumns

[Linnil]

Thanks so much for your response and solution - I really appreciate your response - this was great 🙂

[Linnil]

Thanks for this - I will test it shortly and let you know if I have any questions. I learnt a lot from last time 🙂

[m_dekorte · Accepted solution]

Hi @Linnil,

You can try something like this. Create a new blank query, and replace everything inside with this code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbI0BxJGhvoGRvpGBkYmSrE60UpGQCFjM5C4EYq4MUi9JUjcGEXcBCjkWFBQlF+WmgJkKoAFTUGKLTANN8Om2BzuElSTLTAUxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row/Record" = _t, #"Hours Code" = _t, Date = _t]),
    ShiftHourCode = Table.FromColumns( Table.ToColumns( Source ) & {List.RemoveFirstN( Source[Hours Code] )}, Table.ColumnNames( Source ) & {"t"} ),
    Result = Table.FromRows( List.Transform( Table.ToRows( ShiftHourCode ), 
        (row) => try ( 
            if row{1} = "Approved" 
            then {row{0}, null, null} 
            else if Number.From(row{1}) is number and row{3} = "Approved" 
            then {row{0}, row{1}, "YES"} 
            else {row{0}, row{1}, "NO"} 
        ) otherwise {null, null, null} 
    ), List.FirstN( Table.ColumnNames( Source ), 2) & {"Approved"} )
in
    Result

Returns this result.

I hope this is helpful