As a new Power Query user, I've been doing research on this but I could not find an answer.

I have a column named "Concatenated Data" containing the values below:

• NONONO
• NONOOK
• NOOKNO
• NOOKOK
• OKNONO
• OKNOOK
• OKOKNO
• OKOKOK

And I would like to create the statements (on the right below) into a new custom column to indicate the matching reference:

• NONONO = Missing AM, DM and ML
• NONOOK = Missing AM and ML
• NOOKNO = Missing DM and ML
• NOOKOK = Missing ML
• OKNONO = Missing AM and DM
• OKNOOK = Missing AM
• OKOKNO = Missing DM
• OKOKOK = All is OK

I tried the below for the last statement but failed at concatenating the code:

if Text.Length([Concatenated Data])=OKOKOK then "OK"

As a new Power Query user, I've been doing research on this but I could not find an answer.

I have a column named "Concatenated Data" containing the values below:

• NONONO
• NONOOK
• NOOKNO
• NOOKOK
• OKNONO
• OKNOOK
• OKOKNO
• OKOKOK

And I would like to create the statements (on the right below) into a new custom column to indicate the matching reference:

• NONONO = Missing AM, DM and ML
• NONOOK = Missing AM and ML
• NOOKNO = Missing DM and ML
• NOOKOK = Missing ML
• OKNONO = Missing AM and DM
• OKNOOK = Missing AM
• OKOKNO = Missing DM
• OKOKOK = All is OK

I tried the below for the last statement but failed at concatenating the code:

if Text.Length([Concatenated Data])=OKOKOK then "OK"

• powerbi
• powerquery
• m

3 Answers 3

And a different approach which will adapt to any concatenated length of OKNO.

• Declare the order of equivalences
• Split the OKNO string into two letter lengths
• Change the split list by replacing NO with true and OK with false
• Zip together the Order and the split list
• Select only those splitted lists with true.
• Concatenate to produce the desired result
• The code is a bit longer than it needs to be to provide clarity.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Concatenated Data", type text}}),
    Order={"ML","AM","DM"},
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Missing", each 
        [a=Splitter.SplitTextByRepeatedLengths(2)([Concatenated Data]),
         b=List.Transform(a, each _="NO"),
         c=List.Zip({Order,b}),
         d=List.Select(c, each _{1}),
         e=List.Transform(d, each _{0}),
         f= if List.IsEmpty(e) 
                then "All is OK" 
                else  "Missing " & Text.Combine(e,", ")][f], type text)
in
    #"Added Custom"

Add a new column following the format below (I've filled in the first 3 for you)

Record.FieldOrDefault([NONONO = "Missing AM, DM and ML", NONOOK = "Missing AM and ML" , NOOKNO = "Missing DM and ML"], [Column1], "Default")

I would suggest a different approach, relying on the meaning and logic of each OK/NO:

Edit: a simpler version of the original code which is below:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom Column" = Table.AddColumn(Source, "Reference", each 
        let
            AM = if Text.Start([Column1], 2) <> "OK" then "AM" else null,
            DM = if Text.Middle([Column1], 2, 2) <> "OK" then "DM" else null,
            ML = if Text.End([Column1], 2) <> "OK" then "ML" else null,
            MissingItems = List.RemoveNulls({AM, DM, ML}),
            Result = if List.IsEmpty(MissingItems) then "All is OK" else "Missing " & Text.Combine(MissingItems, " and ")
        in
            Result
    )
in
    #"Added Custom Column"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Added Custom Column" = Table.AddColumn(Source, "Reference", each 
        let
            // Use Column1 instead of Concatenated Data
            AM = if Text.Start([Column1], 2) = "OK" then null else "AM",
            DM = if Text.Middle([Column1], 2, 2) = "OK" then null else "DM",
            ML = if Text.End([Column1], 2) = "OK" then null else "ML",
            MissingItems = List.RemoveNulls({AM, DM, ML}),
            Result = if List.IsEmpty(MissingItems)
                     then "All is OK"
                     else
                         let
                             Count = List.Count(MissingItems),
                             Message = 
                                 if Count = 1 then "Missing " & MissingItems{0}
                                 else if Count = 2 then "Missing " & MissingItems{0} & " and " & MissingItems{1}
                                 else "Missing " & MissingItems{0} & ", " & MissingItems{1} & " and " & MissingItems{2}
                         in
                             Message
        in
            Result
    )
in
    #"Added Custom Column"