Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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
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