Forum Discussion

ghaines's avatar
ghaines
Icon for Resolver I rankResolver I
2 years ago
Solved

Best 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 ...
Need Help
  • spinfuzer's avatar
    spinfuzer
    2 years ago

    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.

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago

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]
        )
    )