Power Query - Remove row - Different Value

[Anonymous]

Hi Team,

Power Query : 

From the below table I have to find the status column with the below criteria,

for one plant and UID there should be only one code , if there is more than one 'code' the status should be 'duplicate' keeping the first code as 'add row' as result.

Below table is the example where there are two code's for same plant and UID.

Thanks,

KV's

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think you can create a custom column, then if you want to filter data, you can remove duplicate rows in pq or use custom column in filter pane then filter in visual.

Here is code in advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnEzMFTSUXIP0TWwtDAyNgGyHQ2VYnVIlTHCJeME0ePoApZxc9c1MDUzNzIGsg3IkQHaEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Plant = _t, UID = _t, Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Plant", type text}, {"UID", type text}, {"Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Code]=Table.First(Table.SelectRows(#"Changed Type",(x)=>x[Plant]=[Plant] and x[UID]=[UID]),"Code")[Code]
 then "add row" else "duplicate")
in
    #"Added Custom"

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 
Add a Blank Query, go to the advanced editor and paste this code and check the steps.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCshJzCtR0lEK9XQBks75KalKsTrRSi5uBoZAvnuIroGlhZGxCZDtaEiGjBEuGSeIHkcXsIybu66BqZm5kTGQbUCODNCeWAA=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {{"Plant", type text}, {"UID", type text}, {"Code", type text}}
  ),
  #"Grouped Rows" = Table.Group(
    #"Changed Type",
    {"Plant", "UID", "Code"},
    {
      {"Count", each Table.RowCount(_), Int64.Type},
      {"All", each _, type table [Plant = nullable text, UID = nullable text, Code = nullable text]}
    }
  ),
  #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 2)),
  #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"All"}),
  #"Expanded All" = Table.ExpandTableColumn(
    #"Removed Other Columns",
    "All",
    {"Plant", "UID", "Code"},
    {"Plant", "UID", "Code"}
  )
in
  #"Expanded All"

[Jihwan_Kim]

Hi, @Anonymous 

I am not sure how your desired outcome looks like, but please check the below for creating a new column and a new table.

Status CC =

IF (

COUNTROWS (

FILTER (

'Table',

'Table'[Plant] = EARLIER ( 'Table'[Plant] )

&& 'Table'[UID] = EARLIER ( 'Table'[UID] )

&& 'Table'[Code] = EARLIER ( 'Table'[Code] )

)

) >= 2,

"Add row",

"Duplicate"

)

New Table =

SUMMARIZE (

FILTER ( 'Table', 'Table'[Status CC] = "Add row" ),

'Table'[Plant],

'Table'[UID],

'Table'[Code]

)

https://www.dropbox.com/s/kxqz07huse5ly8p/udtkv.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim ,

I need Power query calculation ,

Please advise.

Thanks,

KV's