cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Leoaq Regular Visitor
Regular Visitor

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 PlazaACT / INAC
88609181A
88609421I
88609311I
88609171A
88609411A
88609391I
88609371A
88609161A
88609321A
83466821A
MP86749201A
MP86749221I
86452321A
MP86452321A
5313330036I
MP86416591A
86749201A
86749221A
5313330033I
86416591A
86412601A
MP86412601A
86404211A
MP86404211A

 

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jborro Member
Member

Re: Column analysis

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

View solution in original post

8 REPLIES 8
jborro Member
Member

Re: Column analysis

Hi @Leoaq 

 

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

 

jborro Member
Member

Re: Column analysis

@Leoaq 

 

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

Leoaq Regular Visitor
Regular Visitor

Re: Column analysis

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

Leoaq Regular Visitor
Regular Visitor

Re: Column analysis

can this be inserted in a custom column?

jborro Member
Member

Re: Column analysis

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

View solution in original post

mcybulski Member
Member

Re: Column analysis

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"
Leoaq Regular Visitor
Regular Visitor

Re: Column analysis

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

Leoaq Regular Visitor
Regular Visitor

Re: Column analysis

Thanks! this does the job

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,053)