Modify table

Hi marabeda ,

Please see below solution based on screenshot:

1. add a custom step and paste over the code below(a) (The screenshot below shows you where to add the custom step).

(a) Code for custom step:

Table.FillDown(Table.AddColumn(#"Changed Type", "Custom", each if List.ContainsAny(Record.ToList(_), {null}) then List.First(Record.ToList(_)) else null), {"Custom"})

Translate:

1. Record.ToList(_) converts each row into a list.

2. List.ContainsAny(Record.ToList(_), {null}) search through the list at each row for null

3. if the row contains null then List.First(Record.ToList(_)) i.e. get the first value from the list  else null

4. Table.AddColumn() Add a new column

5. Table.FillDown() Fill down the value in the newly added column

Regards

KT

Hi marabeda ,

Please open a blank query and paste the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc0xCsAgDAXQq0jmLmrVg7SbOEjJIKhDtfevSIdAhUCWHx7/ew8Nc8Zb1FhQSNjgu7B5uHLC2uf3wN5TiTVmHOl8RhJJFGxpREm14rSams6q5azmivSvSC+Ldq5op9pw2lBtOW2pdpx2EMIL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cliente/mandato" = _t, Cadenza = _t, Orario = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cliente/mandato", type text}, {"Cadenza", type text}, {"Orario", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Is seller", each if List.Count(List.Select(Record.ToList(_), each  _ <> "" and _ <> null)) = 1 then [#"Cliente/mandato"] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Fill", each [Is seller]),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Fill"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Is seller] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Is seller"})
in
    #"Removed Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.