Forum Discussion
Transform some columns based on condition
Hi,
I have a Table with columns of Material and Consumptions by months and Years , what I want is to check in every rowif the material begins with "TV-" then I would to transform some corresponding Columns to zero and if not it keep the original value, I can not create new column because these are about 25 Columns
Thanks in advance
I second what dufoq3 wrote regarding posting your data as text and also showing your expected results from that data.
But you should be able to adapt the below code to your real data.
Note that this can all be done in a single step using the Table.ReplaceValue function.
Sample Data
let //change next lines to reflect your actual data source Source = Excel.CurrentWorkbook(){[Name="Table31"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}}), #"Replace tv with 0" = Table.ReplaceValue( #"Changed Type", each [Column1], 0, (x,y,z)=> if Text.StartsWith(y,"TV-") then 0 else x, //This line is a list of the columns you wish to have changed to 0 //In my example, I chose all the data columns by removing the first column from the list // but you can be more selective List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),1)) in #"Replace tv with 0"Results
x = current value
y = old value
z = replacement value
IOW:
#"Replace tv with 0" = Table.ReplaceValue( #"Changed Type", each [Column1], //y 0, //z (x,y,z)=> if Text.StartsWith(y,"TV-") then z else x, List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),1)) //xNote that I can replace the 0 with z in the function and will obtain the same result.
See the Help topic for that function for other examples.
4 Replies
Hi,
at the beginning I would like to ask you:
- If you want us to help you - dont paste here sample data as picture but as text so we can copy/paste.
- Dont forget to paste here also expected result
my Sample Source:
I decided to replace columns October 2021 and November 2021 to 0 (for lines starting with "TV-)
Result:
You have to:
- Change YourSource step and refer to your data
- select columns for replace in SelectColumns step
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NY1LCoAwDETv0nUL+WGbpYriQqSL4qb0/tfQRhvIDEkek1rdokh5Q6S040rOu4Cv9IZvaL66cocZEaMEYLC9TrFbVDaT1G0SMfwsZcsHE8+XwTJih/yZCsIkJAq97MaDGt/bAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, #"August 2021" = _t, #"September 2021" = _t, #"October 2021" = _t, #"November 2021" = _t]), YourSource = Source, SelectColumns = List.Buffer({"October 2021", "November 2021"}), MaterialTV = List.Buffer(List.Select(YourSource[Material], each Text.StartsWith(Text.Trim(_), "TV-"))), StepBack = YourSource, #"Added Index" = Table.AddIndexColumn(StepBack, "Index", 0, 1, Int64.Type), TrimmedMaterial = Table.TransformColumns(#"Added Index",{{"Material", Text.Trim, type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TrimmedMaterial, {"Material"}, "Attribute", "Value"), Ad_ReplacedValue = Table.AddColumn(#"Unpivoted Other Columns", "Replaced Value", each if List.Contains(SelectColumns, [Attribute]) and List.Contains(MaterialTV, [Material]) then 0 else Number.From([Value]), Int64.Type), #"Removed Columns" = Table.RemoveColumns(Ad_ReplacedValue,{"Value"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Replaced Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns1"I second what dufoq3 wrote regarding posting your data as text and also showing your expected results from that data.
But you should be able to adapt the below code to your real data.
Note that this can all be done in a single step using the Table.ReplaceValue function.
Sample Data
let //change next lines to reflect your actual data source Source = Excel.CurrentWorkbook(){[Name="Table31"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}}), #"Replace tv with 0" = Table.ReplaceValue( #"Changed Type", each [Column1], 0, (x,y,z)=> if Text.StartsWith(y,"TV-") then 0 else x, //This line is a list of the columns you wish to have changed to 0 //In my example, I chose all the data columns by removing the first column from the list // but you can be more selective List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),1)) in #"Replace tv with 0"Results
Thank you so much, this is exactly what I want , I tried to use this function but I could not do it correctly,
Could you tell me how power query what you mean by x,y,z in this line ?(x,y,z)=> if Text.StartsWith(y,"TV-") then 0 else x
Regards
x = current value
y = old value
z = replacement value
IOW:
#"Replace tv with 0" = Table.ReplaceValue( #"Changed Type", each [Column1], //y 0, //z (x,y,z)=> if Text.StartsWith(y,"TV-") then z else x, List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),1)) //xNote that I can replace the 0 with z in the function and will obtain the same result.
See the Help topic for that function for other examples.
