Forum Discussion
JoaoFidalgo94
1 year agoRegular Visitor
convert null numbers for duplicate lines
Hi everyone! Could you please help me with this issue? For example this table: Ref. Qtd. OK Qtd. NOK Cod. OP Id.1 1533 2 7 A 123456 1533 2 48 B 123456 1533 4 10...
- 1 year ago
Grouping by Id.1 with no aggregation then filling down the Qtd. OK column might work for you.
pbix file attached with your example data. - 1 year ago
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
- Anonymous1 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.
JoaoFidalgo94
1 year agoRegular Visitor
Hi guys! Thanks a lot for your response, it works!