Best way to approach some data

[ghaines]

I'm working with some data that records a primary failure reason ID in one column, and other failure reason IDs as CSV in another column.  The primary failure reason may or may not be represented in the other failure reason column.

My desired outcome is to calculate in DAX Count(VALUES(Transaction_ID)), meaning when there is no filter on failure reasons you will just get the transaction count, so I want to expand the failure reason column while especially preserving the primary failure reason.  So I'd want something like

Transaction ID	Primary Failure Reason	Failure Reason
1	3	3, 4

->

Transaction ID	Failure Reasons
1	3
1	4

and

Transaction ID	Primary Failure Reason	Failure Reason
1	3	4,5

->

Transaction ID	Failure Reasons
1	3
1	4
1	5

Preserving which reason is the primary reason is on the wishlist but not necessarily needed.

My solution so far is to transform Failure Reason to a list, combine with primary failure as a one element list, resolve to a unique list, and then expand to rows.  Is there a more efficient solution, or one that would more naturally identify the primary failure reason?

Thanks in advance.

[AlienSx]

Hello, @ghaines your current approach seems good to me. But if you like complications then try this

    to_list = List.Buffer(Table.ToList(Source, (x) => x)),
    txform = Table.FromRecords(
        List.TransformMany(
            to_list,
            (x) => List.Distinct({Text.From(x{1})} & Splitter.SplitTextByDelimiter(",")(x{2})),
            (id, reason) => [Transaction ID = id{0}, Failure Reason = reason]
        )
    )

[spinfuzer · Accepted solution]

You could

List.RemoveItems(Text.Split([Failure Reason], ","), {[Primary Failure Reason]})

Then add a suffix to the primary reason like ":P"

Combine into a list, split to new rows, split by ":" into columns.

The rows with the "P" in the new column are the primary reasons.

[ghaines]

And with this I could reliably set where the primary failure reason appears.  Thanks!