Forum Discussion

JoaoFidalgo94's avatar
JoaoFidalgo94
Regular Visitor
1 year ago
Solved

convert null numbers for duplicate lines

Hi everyone!

 

Could you please help me with this issue?

 

For example this table:

Ref.Qtd. OKQtd. NOKCod. OPId.1

1533

27A

123456

1533248B123456
1533410C987654

 

Now, my problem is I want to keep the lines but i dont want to duplicate the values on "Qtd. OK" column, is there any way of null the duplicate value on "Qtd. OK" if lines of "Id.1" are the same? Or create a new column like "Ajust Qtd.Ok"? Like this:

 

Ref.Qtd. OKQtd. NOKCod. OPId.1

1533

27A

123456

1533null48B123456
1533410C987654
  • You can select column "Qtd. OK" and from the transform tab pick Fill Down if all the first ref value does not include null, else make group by first, then use fill down

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for the solution Omid_Motamedise  and jgeddes  offered, and i want to offer some more information for user to refer to.

    hello JoaoFidalgo94 , you can create a blank query and put the following code to advanced editor in power query.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NlbSUTICYnMgdgRiQyNjE1MzpVgdFFkTCyDhhFXaBCRqACScgdjSwtzM1EQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref." = _t, #"Qtd. OK" = _t, #"Qtd. NOK" = _t, #"Cod. OP" = _t, Id.1 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref.", Int64.Type}, {"Qtd. OK", Int64.Type}, {"Qtd. NOK", Int64.Type}, {"Cod. OP", type text}, {"Id.1", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Ref.", "Qtd. OK"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
        #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Qtd. NOK", "Cod. OP", "Id.1", "Index"}, {"Qtd. NOK", "Cod. OP", "Id.1", "Index"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded Count",each [Qtd. OK],each if [Index]=1 then [Qtd. OK] else null,Replacer.ReplaceValue,{"Qtd. OK"}),
        #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index"})
    in
        #"Removed Columns"

    Output

    Best Regards!

    Yolo Zhu

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

     

     

4 Replies

  • You can select column "Qtd. OK" and from the transform tab pick Fill Down if all the first ref value does not include null, else make group by first, then use fill down

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi,

    Thanks for the solution Omid_Motamedise  and jgeddes  offered, and i want to offer some more information for user to refer to.

    hello JoaoFidalgo94 , you can create a blank query and put the following code to advanced editor in power query.

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NlbSUTICYnMgdgRiQyNjE1MzpVgdFFkTCyDhhFXaBCRqACScgdjSwtzM1EQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref." = _t, #"Qtd. OK" = _t, #"Qtd. NOK" = _t, #"Cod. OP" = _t, Id.1 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref.", Int64.Type}, {"Qtd. OK", Int64.Type}, {"Qtd. NOK", Int64.Type}, {"Cod. OP", type text}, {"Id.1", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Ref.", "Qtd. OK"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
        #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Qtd. NOK", "Cod. OP", "Id.1", "Index"}, {"Qtd. NOK", "Cod. OP", "Id.1", "Index"}),
        #"Replaced Value" = Table.ReplaceValue(#"Expanded Count",each [Qtd. OK],each if [Index]=1 then [Qtd. OK] else null,Replacer.ReplaceValue,{"Qtd. OK"}),
        #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index"})
    in
        #"Removed Columns"

    Output

    Best Regards!

    Yolo Zhu

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