Forum Discussion

CallumJ's avatar
CallumJ
Frequent Visitor
4 years ago

Multiple values against a single ID, how to create a column that checks and records.

I feel as though this is definitely answered elsewhere but I cannot find the words to search for it.

Basically I have the table below:

 

Doc IDComment
111Test
112Test
111Identifier
112Test
111Test
112Test

 

And I need to create a column that does this:

Doc IDCommentComment Check
111Test2Identifier
112Test1Fail
111IdentifierIdentifier
112Test2Fail
111Test1Identifier
112Test3Fail

 

If any rows against the Doc ID have the identifier, then a new column would show all rows as having the identifier, if it does not, they all fail or just show their previous value (i.e. Test1, Test2, Test3).

Need Help

5 Replies

  • latimeria's avatar
    latimeria
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    Hi CallumJ ,

    You can try this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRCkktLlGK1QFxjVC5IFnPlNS8ksy0zNQi3GqwmhALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Doc ID" = _t, Comment = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Doc ID"}, {{"Row", each _, type table [Doc ID=nullable text, Comment=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each _[Row][Comment]),
    #"Expanded Row" = Table.ExpandTableColumn(#"Added Custom", "Row", {"Comment"}, {"Comment"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Row", "Check", each if List.Contains([Custom], "Identifier") then "Identifier" else "Fail"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Doc ID", "Comment", "Check"})
in
    #"Removed Other Columns"

     

     (copy and paste in a blank query) 

     

     

     

    • CallumJ's avatar
      CallumJ
      Frequent Visitor
      4 years ago

      Hi Latimeria, 

      I am unable to port the code directly into my Power Query as I have done quite a bit with the data (it is sensitive). I can duplicate the query and do this step by step, are you able to provide the steps to completing this?

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    4 years ago

    NewStep=let grp=Table.Buffer(Table.Group(PreviousStepName,"Doc ID",{"n",each if List.Contains([Comment],"Identifier") then "Identifier" else null})) in Table.AddColumn(PreviousStepName,"Comment Check",each grp{[#"Doc ID"=[Doc ID]]}?[n]? ??"Fail")

    • CallumJ's avatar
      CallumJ
      Frequent Visitor
      4 years ago

      Hi Daniel,

      I was unable to port this step into my editor successfully, not sure how to add this as an additional line in my existing code of 10 or so lines.

  • CallumJ's avatar
    CallumJ
    Frequent Visitor
    4 years ago

    Hi Team,

    Unsure if I have given enough information. I have about 15000 lines, in that there are maybe 5500 unique Document ID's. For these they may or may not contain the Identifier in the Comment Column. I already have a large amount of code to get the data to a useable state, so I need something that I can ideally plug in at the end. Worst case scenario I can duplicate the data source and then do a merge on the Document ID.