Copying values from one row to a limited number of rows in another column

[gabrielfernand]

Hello, guys! In my problem I have the table below where I need to copy the ID row to a new ID only column. These labels 01-04 all refer to the same ID, so I need to add a new column with the ID in the same rows as the labels, and after that, if possible remove the id rows in Atributo column. There is no problem if I repeat these values.

Example: 

[rohit_singh · Accepted solution]

Hi @gabrielfernand ,

Assuming your data looks like below, please try the following steps :

1) Add a conditional column. If atributo = "id" then valor else null. This will add the id values to the new column.

2)Perform a fill down operation on the ID column. This will copy the prevous non-null value to the current row.
 

3) Filter column Atributo <> "id". This will filter out all rows with value "id".

This will give you the expected result.

Here is the M-Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxR0lEytDAxBlIGSrE60Uo+iUmpOfEGhmABoByyoBFU0AhZ0BgqaIwsaAIVNAELwmwBGWqKzRYzbLaYY7PFApstlii2gJ2N3TNYfWOI1TuGWP1jCPRQLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Atributo = _t, Valor = _t, Indice = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Atributo", type text}, {"Valor", Int64.Type}, {"Indice", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ID", each if [Atributo] = "id" then [Valor] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"ID"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Atributo] <> "id"))
in
    #"Filtered Rows"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[amitchandak]

@gabrielfernand , Replace all 0 with blank in valor column or create a new column in power query

id = if  [Atributo] ="id" then [valor] else null

then use fill down and remove id rows  using filter 

Fill Up Fill Down: https://youtu.be/mC2ps0pFqBI