Forum Discussion
Resolver IBest way to approach some data
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.
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.
3 Replies
spinfuzerSolution Sage
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!
- AlienSx
Super User
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] ) )
