Forum Discussion

Nico_Schulze's avatar
Nico_Schulze
Frequent Visitor
3 years ago

Use every seventh row

Hey yall,    I have an Column called "A" that has 100 Entries. In Column "B" I want to only use every seventh ROW, so ROW 7,14,21...    Does someone know how I can do this?    Thnak you very mu...
Need Help
PhilipTreacy's avatar
PhilipTreacy
Super User
3 years ago

Hi Nico_Schulze 

 

Download example PBIX file

 

try this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcy7EYAwEAPRXi4mYA/MpxaP+28DRps8Rdo5i1rbrI5HPOOIV7zjE9/I7vjGO/4xgAVMYAMjWGkr/VfWBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "B", each if Number.Mod([Index], 7) = 0 then [A] else "")
in
    #"Added Custom"

 

 

What you need to do is add an Index column and then use Number.Mod to check if the Index for that row leaves 0 remainder when divided by 7.  If so then use the value from A else use a ""

 

regards

 

Phil