Forum Discussion

lucasib's avatar
lucasib
New Member
4 years ago
Solved

Change rows

Hello. I only need to put row 2 of the "NSU" table in row 1. I know this might be an easy question to solve, but I started learning recently. I don't need the lines with the code starting at 013......
Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi lucasib ,

     

    Please follow the steps below in Power Query :

     

    1) Select the "Date" column and go to "Transform" --> "Fill" --> "Down"

    Repeat this for the "Valor" and "Opercao" columns

     

    This will replace the null values with the last non-null value in the rows above.

     

    2) Select the "NSU" column and click on the filter button. Select "Text Filters" --> "Does not begin with" and add 130 as the value as shown below.

     

    3) All rows starting with 130 will be filtered out and you will get the desired output.

     

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! ğŸ˜Š

     

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Lots of ways to do it, like Table.Split(TableName, 2) to make a list of two-row tables that you can select fields from, but the dead simple way is to use the Table.FillDown function, then just use Table.Alternate to keep just the alternate rows.

 

-Nate

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Table.FillDown is great here but I'd be worried about inconsistent parity causing issues with Table.Alternate (e.g. if there were an extra or missing row somewhere).

 

This can be fixed by adding a custom column that tags which rows to keep based on Data and Valor being null. For example,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31TcyMDJS0lEyMDQ2MDYyNTAwtQDyjIyMjfQsTYCskMyS0px8pVidaCUgz9k/wNPRxT/IMRjIASGQOI1NMbMwN9MzI9IUKJOQmUAjDY30TCyIMDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, NSU = _t, Valor = _t, Operacao = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type date}, {"NSU", type text}, {"Valor", type number}, {"Operacao", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Operacao"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Data] = null and [Valor] = null then "Keep" else "Remove", type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Data", "Valor", "Operacao"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Custom] = "Keep" and [NSU] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"