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

 

Regards 

Nico

Need Help

8 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

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

    Stéphane 

    • Nico_Schulze's avatar
      Nico_Schulze
      Frequent Visitor
      3 years ago

      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

       

    • Nico_Schulze's avatar
      Nico_Schulze
      Frequent Visitor
      3 years ago

      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

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    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

  • 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