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
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
can this be inserted in a custom column?
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
Thanks! this does the job