Forum Discussion

micjensen's avatar
micjensen
Frequent Visitor
4 years ago
Solved

Repeat Switching/Changing Values in a Column

Hi Community I have a column "Transport Out/Home" where i need help to fill in values for the null cells. In the column i only distinguish between 2 values: 'Out' & 'Home'. I want a result where the...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the sample code here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvLVGK1YGwMRke+bmpYI4TklInmAoUBlypM5JSZ5gKF2QGXKkLklJXmApMhhuM4Y6s2R1JsztYRSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp Name" = _t, #"Transport Out/Home" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Emp Name", "Transport Out/Home"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Emp Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index1",0,1), type table [Emp Name=nullable text, #"Transport Out/Home"=nullable text, Index1=number]}}),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Transport Out/Home", "Index1"}, {"Transport Out/Home", "Index1"}),
    Custom1 = Table.ReplaceValue(#"Expanded Temp",each [#"Transport Out/Home"], each {"Out","Home"}{Number.Mod([Index1],2)},Replacer.ReplaceValue,{"Transport Out/Home"})
in
    Custom1
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Insert one Index column with start of 1 and insert this statement

= Table.ReplaceValue(#"Added Index",each [#"Transport Out/Home"],each if [#"Transport Out/Home"]<>null then [#"Transport Out/Home"] else (if #"Added Index"[#"Transport Out/Home"]{0} ="Out" then {"Home","Out"} else {"Out","Home"}){Number.Mod([Index],2)},Replacer.ReplaceValue,{"Transport Out/Home"})

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("i45WMjQyVtJR8i8tUYrVAfFMgDwo0xTBNAMyPfJzU6FccxQtFgh1lnCmsQGKFmNDZC3GRgh1xgimCaoWUxQtZgh15ggmwm5jhN0mqHaboNhtArE7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Emp ID" = _t, #"Transport Out/Home" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Emp ID", "Transport Out/Home"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
    Custom = Table.ReplaceValue(#"Added Index",each [#"Transport Out/Home"],each if [#"Transport Out/Home"]<>null then [#"Transport Out/Home"] else (if #"Added Index"[#"Transport Out/Home"]{0} ="Out" then {"Home","Out"} else {"Out","Home"}){Number.Mod([Index],2)},Replacer.ReplaceValue,{"Transport Out/Home"})
in
    Custom