Column analysis

[Anonymous]

Hello i have the next problem, i need to determine if 2 rows have the letters A here is my sample data 

Clave de Plaza	ACT / INAC
88609181	A
88609421	I
88609311	I
88609171	A
88609411	A
88609391	I
88609371	A
88609161	A
88609321	A
83466821	A
MP86749201	A
MP86749221	I
86452321	A
MP86452321	A
5313330036	I
MP86416591	A
86749201	A
86749221	A
5313330033	I
86416591	A
86412601	A
MP86412601	A
86404211	A
MP86404211	A

I need to do this in power query, the problem is that the column [Clave de Plaza] is an ID for a workplace but every workplace has a mirror one starting with the letters "MP" so i need to analize if the rows ID and "MP"+ID have both A then return me an error because the correct combinations can be I/I or A/I or I/A 

[Anonymous]

The attached query is another solution.

let
    Source = Table,
    AddMP = Table.AddColumn(Source, "MP", each if Text.Start([Clave de Plaza],2) = "MP" then "MP" else ""),
    #"Replaced Value" = Table.ReplaceValue(AddMP,"MP","",Replacer.ReplaceText,{"Clave de Plaza"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[MP]), "MP", "ACT / INAC", List.Min),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Error", each if [#""] = "A" and [MP] = "A" then "AA Error" else null)
in
    #"Added Custom"

[Anonymous]

It almost worked, the problem is that this code was taking ou the "MP"s from my original column [Clave de Plaza] and i can't do that

[Anonymous]

Hi @Anonymous 

there are some considerations that may impact on the solution:

1. Is this guaranteed that there is a pair ID & MP+ID? Or ID can be without MP+ID and vice versa?

2. Is this guaranteed that there is only one unique ID and one unique MP+ID in the entire table?

3. If #1 is not guaranteed how do you want to treat single ID or MP+ID - error? or ignore?

4. If there is more than one unique ID or MP+ID how this is treated? All need to be of opposed type? Eg. three IDs (A/A/A) and MP+ID (I/I/I)? or if at least one opposed pair we are Ok (e.g. ID(A/A/A) and MP+ID (A/I/A)?

Thanks,

JB

[Anonymous]

there can be ID witouth MP+ID but those ID's are ignored, what i only need to analize is the pairs ID & MP+ID, thanks

[Anonymous]

@Anonymous 

this code covers the most simplistic scenario:

1. There is only one unique ID or MP+ID in the table.

2. ID and MP+ID without a corresponding pair are ignored.

Pass = TRUE means that we have a pair of type A/I or I/A or I/I. FALSE means that this is a pair type A/A.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdA7DsNACATQu2ztAhgWs2XKFJHcW77/NfyRZaNJ+8QMiHVtmSFDU9vUPm2bbnA74fsClEBnjigBBndwRIMjVgAekQV+S8bsw+Sf6rXh3UAxog4FIIJ4YteURh/lAN5WdlEP6npucbWgm4kOkOPjNFNo2wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Clave de Plaza" = _t, #"ACT / INAC" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clave de Plaza", type text}, {"ACT / INAC", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","MP","",Replacer.ReplaceText,{"Clave de Plaza"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Clave de Plaza"}, {{"Count", each Table.RowCount(_), type number}, {"Pass", each List.ContainsAll(_[#"ACT / INAC"], {"I"}), type logical}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Count] = 2))
in
    #"Filtered Rows1"

Kind regards,

JB

[Anonymous]

can this be inserted in a custom column?

[Anonymous · Accepted solution]

Do you mean something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdA7DsNACATQu2ztAhgWs2XKFJHcW77/NfyRZaNJ+8QMiHVtmSFDU9vUPm2bbnA74fsClEBnjigBBndwRIMjVgAekQV+S8bsw+Sf6rXh3UAxog4FIIJ4YteURh/lAN5WdlEP6npucbWgm4kOkOPjNFNo2wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Clave de Plaza" = _t, #"ACT / INAC" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clave de Plaza", type text}, {"ACT / INAC", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Clave de Plaza", "Temp"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","MP","",Replacer.ReplaceText,{"Temp"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Temp"}, {{"Count", each Table.RowCount(_), type number}, {"Pass", each List.ContainsAll(_[#"ACT / INAC"], {"I"}), type logical}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Count] = 2)),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Temp"}, #"Filtered Rows1", {"Temp"}, "Filtered Rows1", JoinKind.LeftOuter),
    #"Expanded Filtered Rows1" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows1", {"Pass"}, {"Pass"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Filtered Rows1",{"Temp"})
in
    #"Removed Columns"

Kind regards,

JB

[Anonymous]

Thanks! this does the job