Forum Discussion
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... I just need the date, NSU, value and operation to be on the same line.
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! 😊
4 Replies
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"
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! 😊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"
