Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Conditional changes to multiple columns at once

Hello there.  I'm doing a bit of data cleansing and am hoping for a pointer on the following scenario. Two columns containing YYYY data with some punctuation (sample below).  Not all entries have da...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Add column as below

    Write code in Advanced editor

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0MFXSUVKK1QGxjS2BbENLEyMoX1cXLBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YearFrom = _t, YearTo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearFrom", type text}, {"YearTo", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "flag", each if Text.Contains([YearFrom], "--") then 1 else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column","--","00",Replacer.ReplaceText,{"YearFrom"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "century", each Number.FromText([YearFrom])+100-1),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom" , "modified_YearTo", each if [flag] = 1 then [century] else [YearTo])
in
    #"Added Conditional Column1"

    Then remove [flag],[century],[YearTo] columns.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous 

Add column as below

Write code in Advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrS0MFXSUVKK1QGxjS2BbENLEyMoX1cXLBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YearFrom = _t, YearTo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearFrom", type text}, {"YearTo", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "flag", each if Text.Contains([YearFrom], "--") then 1 else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column","--","00",Replacer.ReplaceText,{"YearFrom"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "century", each Number.FromText([YearFrom])+100-1),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Custom" , "modified_YearTo", each if [flag] = 1 then [century] else [YearTo])
in
    #"Added Conditional Column1"

Then remove [flag],[century],[YearTo] columns.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.