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 8...
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
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"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