Repeat Switching/Changing Values in a Column

[micjensen]

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 values are changing/switching from one another - so first row value is Out, then next null value should be Home and then next null value should be Out etc. pls. see my screenshot for explanation on the wanted output 🙂  I really hope there is a genius out there, because i'm about to explode !!! 😄

[Vijay_A_Verma]

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

[micjensen]

Hi Vijay

We are getting very close here. However i do find some weird results when following your example. This is my full query from advanced editor:

let
Source = Excel.CurrentWorkbook(){[Name="Project_Transactions"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee No", Int64.Type}, {"Employee Name", type text}, {"Account Date", type datetime}, {"Activity Sequence", Int64.Type}, {"Activity Short Name", Int64.Type}, {"Country", type text}, {"Project ID", Int64.Type}, {"Project Name", type text}, {"Project Description", type text}, {"Transferred To Payroll", type logical}, {"Payroll ID", type any}, {"Sub Project Name", type text}, {"Activity ID", Int64.Type}, {"Activity Description", type text}, {"Report Code", type text}, {"Report Code Name", type text}, {"Sub Project ID", Int64.Type}, {"Report Code Type", type text}, {"Price Adjustment ID", type any}, {"Price Adjustment Name", type any}, {"Internal Quantity", type number}, {"Internal Price", Int64.Type}, {"Overhead Price", Int64.Type}, {"Total Internal Price", Int64.Type}, {"Alternative Cost", Int64.Type}, {"Internal Amount", type number}, {"Sales Quantity", type number}, {"Currency Code", type text}, {"Currency Type", Int64.Type}, {"Sales Price Basis", Int64.Type}, {"Markup Price", Int64.Type}, {"Sales Price", type any}, {"Sales Price In Accounting Currency", Int64.Type}, {"Sales Amount Basis", type number}, {"Markup Amount", Int64.Type}, {"Sales Amount", type number}, {"Sales Amount In Accounting Currency", type number}, {"Internal Comments", type text}, {"Invoice Comments", type any}, {"Transaction Source", type text}, {"Invoicability", type text}, {"Invoice ID", Int64.Type}, {"Invoice No", Int64.Type}, {"Invoice Status", type text}, {"Organization Code", Int64.Type}, {"Create Cost Accounting", type text}, {"Approved", Int64.Type}, {"Cost Accounting Voucher Created", Int64.Type}, {"Correction Status", type text}, {"Report Code Group ID", Int64.Type}, {"Report Code Group Name", type text}, {"Day Confirmed", Int64.Type}, {"Billing Category", type any}, {"Billing Category Description", type any}, {"Customer", Int64.Type}, {"Customer Name", type text}, {"Created By", type text}, {"Redistributed By", type any}, {"Redistributed By Name", type any}, {"Registered By", type text}, {"Registered By Name", type text}, {"Jinsui Enabled", type logical}, {"Delivery Type Description", type any}, {"Delivery Type", type any}, {"Resource ID", type any}, {"Resource Description", type any}, {"Program ID", type text}, {"Program Name", type text}, {"Multi-Company Transaction Type", type any}, {"Revenue Accounting Voucher Created", Int64.Type}, {"Customer Transaction Approved", Int64.Type}, {"Multi-Company Reporting Company ID", type any}, {"Employee Invoice No", type any}, {"Project Transaction Seq", Int64.Type}, {"Approved For Payroll", type logical}, {"Created By Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Report Code identifier", each Text.End([Report Code], 2)),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Report Code identifier", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Report Code identifier] = "30")),
#"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Employee Name", Order.Ascending}, {"Account Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Duplicate_Trans ud/hjem", {"Index"}, "Duplicate_Project_Transactions", JoinKind.LeftOuter),
#"Expanded Duplicate_Project_Transactions" = Table.ExpandTableColumn(#"Merged Queries", "Duplicate_Project_Transactions", {"Employee No"}, {"Duplicate_Project_Transactions.Employee No"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Duplicate_Project_Transactions",{{"Duplicate_Project_Transactions.Employee No", "Emp No Prev Row"}}),
#"Sorted Rows2" = Table.Sort(#"Renamed Columns",{{"Index", Order.Ascending}}),
#"Merged Queries1" = Table.NestedJoin(#"Sorted Rows2", {"Index.1"}, #"Duplicate_Trans ud/hjem", {"Index.1"}, "Duplicate_Project_Transactions", JoinKind.LeftOuter),
#"Expanded Duplicate_Project_Transactions1" = Table.ExpandTableColumn(#"Merged Queries1", "Duplicate_Project_Transactions", {"Employee No"}, {"Duplicate_Project_Transactions.Employee No"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Duplicate_Project_Transactions1",{{"Duplicate_Project_Transactions.Employee No", "Emp No Next Row"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Transport Out/Home", each if [Employee No] <> [Emp No Prev Row] then "Out" else if [Employee No] <> [Emp No Next Row] then "Home" else null),
#"Added Index2" = Table.AddIndexColumn(#"Added Custom1", "Index.2", 1, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index2",each [#"Transport Out/Home"],each if [#"Transport Out/Home"]<>null then [#"Transport Out/Home"] else (if #"Added Index2"[#"Transport Out/Home"]{0} ="Out" then {"Home","Out"} else {"Out","Home"}){Number.Mod([Index.2],2)},Replacer.ReplaceValue,{"Transport Out/Home"})
in
#"Replaced Value"

[Vijay_A_Verma · Accepted solution]

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