Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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]
)
)
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.
And with this I could reliably set where the primary failure reason appears. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.