Use every seventh row

[Nico_Schulze]

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 much for your help! 

Regards 

Nico

[slorin]

Alternate, not Alternative

https://learn.microsoft.com/fr-fr/powerquery-m/table-alternaterows

Stéphane

[slorin]

Hi

= Table.AlternateRows(Source,0,6,1)

Stéphane 

[Nico_Schulze]

Hey @slorin , 

your solution seems easy. But when I try to write it down in my Power BI, it doesnt work. The function Table.AlternativeRows doesnt exist. What am I doing wrong? 

Thnaks for the help!

Regards

Nico

[PhilipTreacy]

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

[PhilipTreacy]

@Nico_Schulze 

What I mean is, in Column B rows 1 to 6, what do you want there?  A blank?

If you are adding another column, it has to have the same number of rows as all the other columns, so what do you put in the rows of that column from 1 to 6, 8 to 13 etc?

Phil

[Nico_Schulze]

Hey @PhilipTreacy, 

sorry for the misunderstanding. Yes a Blank would be awesome! 

Regards 

Nico

[PhilipTreacy]

Hi @Nico_Schulze 

So what do you have in the other rows i.e. 1-6, 8-13 etc?

Phil

[Nico_Schulze]

Hello Philip, 

in the other ROWS I also have Entries but they are not relevant for me. I only want to use every seventh Row. 

Regards

Nico