Custom Column Based On Multiple Column Values And Other Rows

[boxfan]

Hi! I'm working with PQ in Power BI and need to ingest a spreadsheet and create new columns based on the sheet's data. The data consists of a team's transactions over a period of time and I need to find out if any individual team member made a transaction of Monday and the preceeding Friday. So far I have created columns to:

• Add the transaction day of week (Monday, Tuesday, etc.)
• Determine if the day of week is a Monday (True/False)
• Determine the date of the preceeding Friday if Monday = true

I am having trouble creating a new column that uses the searches all the records in the spreadsheet for each row to find out if that user made a transaction the preceeding Friday. I've attempted using Table.RowCount(Table.SelectRows()) to return a "Yes/No" in the new column but am not having much luck. I'm currently only receiving Errors or zeroes when just returning the rowcount.

Any help would be appreciated, thanks.

[Chewdata]

Hey!

This solution uses a join with itself to get records from the previous friday. I tried to explain step by step what i've done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01DWw0DUyMDJVitWBChlhChljCJljiFjoouuywDTIEkXICeoCczQhE0whM4RQLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, TransactionDate = _t]),
    set_types = 
    Table.TransformColumnTypes(
        Source,
        {
            {"User", type text}, 
            {"TransactionDate", type date}
        }
    ),
    // convert the transaction date to a number
    add_DateId = Table.AddColumn(set_types, "DateId", each Number.FromText(Date.ToText([TransactionDate], "yyyyMMdd")), Int64.Type),

    // Add indication if date is a Monday
    add_Monday = Table.AddColumn(add_DateId, "Monday", each Date.DayOfWeek([TransactionDate], Day.Monday) = 0, type logical),

    // if day is monday, add the dateID for the previous friday by subtracting 3
    add_PrevFridayId = Table.AddColumn(add_Monday, "PrevFridayId", each if [Monday] = true then [DateId] - 3 else null, Int64.Type),

    // Join with self to get records from prev friday
    leftouter_self = Table.NestedJoin(add_PrevFridayId, {"User", "PrevFridayId"}, add_PrevFridayId, {"User", "DateId"}, "TablePrevTransaction", JoinKind.LeftOuter),

    // add indication if a transaction is found by checking if list (column from nested table) is empty
    add_TransactionFound = Table.AddColumn(leftouter_self, "Transaction found", each List.IsEmpty([TablePrevTransaction][DateId]) = false, type logical)

in
    add_TransactionFound

[jgeddes · Accepted solution]

Here is a method that may work for you using Table.Group.
Example data.

Result

Example code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbLQNzTUNzIwMlWK1YGLGGGIGKOLmKMLWGDowRSxRIg4AUXMUeyGiphgiJhBRWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, TransactionDate = _t]),
    set_types = 
    Table.TransformColumnTypes(
        Source,
        {
            {"User", type text}, 
            {"TransactionDate", type date}
        }
    ),
    group_rows = 
    Table.Group(
        set_types, 
        {"User"}, 
        {{"AllRows", each _, type table [User=nullable text, TransactionDate=nullable date]}}
    ),
    add_nested_prevFriday_column = 
    Table.TransformColumns(
        group_rows, 
        {
            {
                "AllRows", 
                each 
                Table.AddColumn(
                    _, 
                    "prevFriday", 
                    (r)=> 
                    if Date.DayOfWeek(r[TransactionDate], Day.Sunday) = 1 and List.Contains([TransactionDate], Date.AddDays(r[TransactionDate], -3)) 
                        then true 
                        else false, 
                    type text
                ), 
                type table [TransactionDate=date, prevFriday=logical]
            }
        }
    ),
    expand_rows = 
    Table.ExpandTableColumn(
        add_nested_prevFriday_column, 
        "AllRows", 
        {"TransactionDate", "prevFriday"}, 
        {"TransactionDate", "prevFriday"}
    )
in
    expand_rows

[GeraldGEmerick]

@boxfan Perhaps you can Merge the table with itself using your previous Friday date and the transaction date? You might need to create some custom join columns if you also need things like the team member, etc.