Forum Discussion

osama_ayoub's avatar
osama_ayoub
Helper III
2 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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 

     

     

     

     

     

  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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))    //x

     

    Note 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.

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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"