Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gabrielfernand
New Member

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

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.

gabrielfernand_0-1654181054240.png

Example: 

gabrielfernand_2-1654181621683.png

 

 

1 ACCEPTED SOLUTION
rohit_singh
Solution Sage
Solution Sage

Hi @gabrielfernand ,

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

rohit_singh_0-1654182307214.png

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

rohit_singh_3-1654182476734.png

 

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

rohit_singh_6-1654183484507.png

rohit_singh_7-1654183544360.png

 

 

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

rohit_singh_4-1654182517668.png

This will give you the expected result.

rohit_singh_5-1654182552404.png

 

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! 🙂

View solution in original post

2 REPLIES 2
rohit_singh
Solution Sage
Solution Sage

Hi @gabrielfernand ,

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

rohit_singh_0-1654182307214.png

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

rohit_singh_3-1654182476734.png

 

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

rohit_singh_6-1654183484507.png

rohit_singh_7-1654183544360.png

 

 

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

rohit_singh_4-1654182517668.png

This will give you the expected result.

rohit_singh_5-1654182552404.png

 

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
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.