Keep one in every x values in a column

Hi Anonymous ,

 

Paste this into a new blank query to follow my steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslDSwYFjdUZlB4FsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [mon = _t, tue = _t, wed = _t, thu = _t, fri = _t, sat = _t, sun = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"mon", Int64.Type}, {"tue", Int64.Type}, {"wed", Int64.Type}, {"thu", Int64.Type}, {"fri", Int64.Type}, {"sat", Int64.Type}, {"sun", Int64.Type}}),
    addIndex = Table.AddIndexColumn(chgTypes, "Index", 0, 1, Int64.Type),
    addIndexRepeat = Table.AddColumn(addIndex, "indexRepeat", each Number.Mod([Index], 7) + 1),
    addNewMon = Table.AddColumn(addIndexRepeat, "newMon", each if [indexRepeat] = 1 then [mon] else null),
    addNewTue = Table.AddColumn(addNewMon, "newTue", each if [indexRepeat] = 2 then [tue] else null),
    addNewWed = Table.AddColumn(addNewTue, "newWed", each if [indexRepeat] = 3 then [wed] else null),
    repFri = Table.ReplaceValue(addNewWed,each [fri], each if [indexRepeat] = 5 then [fri] else null, Replacer.ReplaceValue,{"fri"}),
    repSat = Table.ReplaceValue(repFri, each [sat], each if [indexRepeat] = 6 then [sat] else null, Replacer.ReplaceValue,{"sat"}),
    repSun = Table.ReplaceValue(repSat, each [sun], each if [indexRepeat] = 7 then [sun] else null, Replacer.ReplaceValue,{"sun"})
in
    repSun

 

There's two different ways of getting the output once a repeating index has been created.

 

The steps 'addNewMon - addNewWed' show how to creat new columns with your desired output.

The steps 'repFri - repSun' show how to replace values in the original columns with you desired output.

 

Pete

try this