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

     

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31TcyMDJSMDSyMjAAIgVHXyUdJUMjIGECxK5KsTrRSnmlOTlATmJSMpCEcsASOLUbA0kLIHZC1p+ckkqc/tTUotQiIG0JxFHIJhiaGBubGpsimxILAA==", 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 datetime}, {"NSU", type text}, {"Valor", Currency.Type}, {"Operacao", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    BuffList = List.Buffer(#"Added Index"[NSU]),
    Custom = Table.ReplaceValue(#"Added Index",each [NSU],each if Number.Mod([Index],2)=0 then BuffList{[Index]+1} else [NSU],Replacer.ReplaceValue,{"NSU"}),
    #"Removed Columns" = Table.RemoveColumns(Custom,{"Index"})
in
    #"Removed Columns"