Keep one in every x values in a column

[Rahul94]

Hi Community,

I duplicated my data because I had to split dates. At the moment my data is as follow:

I need to get my data as follow:

I was wondering if this is possible, and if so, how can I do this? 

[Anonymous]

try this

[Rahul94]

after trying to do what you did I get the following error:

[Anonymous]

You should show all the script leading to that error, otherwise a targeted analysis cannot be done.

Somehow camouflage sensitive data and copy paste the scripts you use.

[Rahul94]

I made a new dataset in order to test your approach:

[Anonymous]

= Table.FromRecords(Table.TransformRows(Table.AddIndexColumn(prevtab,"idx",0,1), (r)=>Record.FromList(shiftdays(Number.Mod(r[idx],7)),daysname)))

il mio approccio è diverso da quello che hai usato tu e che riporti nella terza immagine (sarebbe preferibile copiare il testo in modo da consentire di editarlo).

Sembra che tu abbia scambiato PREVTAB e daysname.

Devi cambiare il nome  PREVTAB mettendo il nome della tua tabella ed usare gli altri tre pezzi che trovi nel mio file.

[BA_Pete]

Hi @Rahul94 ,

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

[Rahul94]

I get the following error:

[BA_Pete]

Hi @Rahul94 ,

You need to create a new blank query in Power Query, then go to the Home tab > Advanced Editor. Once the code window is open, paste my code oer all the default code that's in there.

Pete

[Rahul94]

I am new with working with powerquery, can you help me how to implement your solution in my current query:

let
Source = SharePoint.Tables("XXXXX", [Implementation="2.0", ViewMode="All"]),
#"dca7bbdd-3e86-4c77-95c3-980301dd0d44" = Source{[Id="dca7bbdd-3e86-4c77-95c3-980301dd0d44"]}[Items],
#"Added Index" = Table.AddIndexColumn(#"dca7bbdd-3e86-4c77-95c3-980301dd0d44", "Index", 0, 0, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Employee1", each [Employee]{[Index]}[title]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [BillTo]{[Index]}[lookupValue]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "weekstart_m", each Date.AddDays([Week Start],1)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Weekeind", each Date.AddDays([Week Start],7)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"weekstart_m", type date}, {"Weekeind", type date}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type", "Dates", each { Number.From([weekstart_m])..Number.From([Weekeind]) }),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom4", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Mon", "mon"}, {"Tues", "tue"}, {"Weds", "wed"}, {"Thurs", "thu"}, {"Fri", "fri"}, {"Sat", "sat"}, {"Sun", "sun"}})
in
#"Renamed Columns"