Forum Discussion
Column analysis
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
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
8 Replies
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
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
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"