Power Query Tables

[M_SBS_6]

Hi, I have connected to an excel file and within the 1 sheet, there are 3 tables. Each month a new row is added to each table but the column headers always stay the same. 

I only need to bring in the top table but in order to do this, I essentially need to say that from row where column1 = test errors and below, then exclude else keep. 

any idea how to do this please? 

[danextian]

Hi @M_SBS_6 

Assuming each month section has its own header, identify the row position of that header after the top table and use that index to extract the corresponding rows. If the monthly sections share the same header as the top table, instead locate the first blank or null row—whichever applies—provided that column values do not contain nulls.

Sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VjLblzJDf0VQevBFavIYrGWySDxaoAATlYDLxp2JxFitQJJE2T+PoeP2+qJ2l4kgFcGBKj61ovPw8P6+efbH/9+OJ2On2/+dHh6OR2fnm9/uP3xcDp8OmDQVxu6NcVwNNOxifjXvrht5MNGQ9fWOoasnWxTH3ZSm7lNldq2Yt6oty3391n7lYR9Uvpac1Pz2Ua9LmqzqW5+DJMMy82DZWyxaQzpuvkeWa2pbE1uP/xwVaM/Ph1OH4++XXiNrcdFY9iqIa3V8iKWtg0/vdlopVpvXVv8l7k2DdGWQDH1LZBcUyARnLkfvkg2Tokhpx/dJswmMWtz1DU4mXWbMRRrnFJMSEQb65f0eXd8ejicfo1NE2KGSNaJ0uptsXF8kmlb3mgQbcRkn72XLVfIzUIxs5RW3U9GoVB4qc+5YKnwCOwfSxqRlWe76qpbWoNe4TtmhTU2sy+p8NPx3/cfH+OkyRzyGLUyH5Px2MiHODFkgaGa5LIGM2UEQIoIKKa+9RDV2tkIviONPJa2nG/dfKmHHvNyC+O8oRTi235DH4IoyXBkYTidvqjHX073L8dPN+9fDi/H55vHv9787uH4dP8x0gc5sVJSY55lt5VJRHNJGY3HqmDAV67MQgplcLsEms5EAqXBDTsslg0E4rYy5DTdAX9w2g7JJ7KtzKDZ9rQyRQrFR1Wk09ZSOXz4X/7+z63vj397OJ5eHHYefzm9PP0awt7RuuvUOPLmrtH5B921djnTL35QzGQY3FHff/gMn3/gaLmcGZczejkzL3/Y5bJ1MZOyyaVscimb/3gH+Pl08+fHl8Pn0PkPp5fj0z+f7p8dkl7RVsdUj2fTPijOYAy3EUEyRnxDMkjTrfvH1uBKylkCfvq3RWvs06t1IE6MWcRzJIbMzXIx4r33WqEiqzaKTYBJ3msyWg5VcVkcsHQCcX3tG2XOQIs4XzM3NgWcpZgQiLeZH/E1bmAeu5QdWJkrjXuzXDmajA4szW2I911iYPAsMXl4Ekd+y+RUbhJ0riOEWz8bEjgZxu27zdgW4Q/F64pGr1Db3Ael0sIhcdwiTWEIptyFQQ0rU65hu3JrSOg2+pQSsU8DXoUElNYQAJ/VXpTZtUqDLl4oasyA67xqoCgHUDdAdu1X63rVNxeIO2DzMg1MnrcRHBGyEPeR8mlT3n2Iml9DYGxLW5tZSs+6L+ttUXlyD6c2Z+E0DFCHCGwQbkCUjHJ0n+6TiFA4ya4H2Ffg1m1Xca1Cq/xNfYVaXpFrWkerEXeebffaYEptpu6xB0tPDl1RAcsUSKS5uxeuLvfPlfnZEXWVXT41/ZuyovjX5kGeRd/x9lvi7bvHfx2fTrvaZ7xFboKyUsLmDB4VpBTIATQISqSCeMaa6YDCTUAQuhfZCVAC9gRvY0QyyF4QFzCp5WDlS6YTw+B7iEendFuL04dgZ7JENuc5wS1VDCQlLnWuBnYZJRyJ7wmiyRlkAXmLgdIUwKk6c3ij4xmGx2rOJM2vgMjk5T54DdgZTouroSJOCuIzoArIQ1gEiAiRLci/YUUJBzLYZ1lnoBCAVq4Sx3pSqw72hOTn5DGQoKXISwFuMFqwmTFmR46GxZGP1nmzGRNgjkNCsgbmPWfa+Y2Or8AMKtcdNEM8dCxIsJAJBnKxw6d+A6ROnJEGHHcroGx2NAFUrKqjso6VJiFYPZiTrLU3H9PC68Ek0UgsGLDgxoE62BfUdTqXLBxYbMkooZWk29CpOFrmGJXNwDSXvFXvjNbdhmiKCyiTuXM7GAaXRhzhGkVkxgUG7yCQZthl9ZF9jcBV8HE0LGzNaUXEvdkEelevBuBHybaIYwO/hwGiRCDmdKYZQbW12g1iTFBcBOSFn1wNOBnNHVZHHq6JBhKtA9tb/b4C5A1kGQJGhHUe4K1Wl5tY0WPkIm6c0SzAhi5IC1+gEAK9I0mVyPusHiI6B4FCAesTgnvwJ9kR92MGpTdMXvt8B4iGM39OryLOM1EVS9AJZ0ub2TLDNaACnvbGwBJB19y+g/w3Bfmf7j89HJ7+cfwNxoPqqivi5DbjtiNWIwsQTUHH0CfGBCO1NLEY7LN6M9R/yxwHNZdAFTSQlP0iCN/w1j+ZhPZZpKBxdstICwRC8gSpbg8tt+S1hMYv4vG/hT+DNzAxSR5gKkJY0RHmZaMQCVQTyZbcGAev6utA2jmvAxglljLSuxX92UVoko03mCZVsjOa8ewSUGuqYGm+tmTjCgW8haYrgr8iMuDIrT7Usz/TEuQvTYEm3+dWW7xzd83HBQBTyGdWTH36QrSzIS7sGz9l+HtLZKuTvJVUee0MdA1QrSgk+FTPKQhm9MFXJD6DrE0sSAbuhqoyhvrqWlDAo9BECCXbE03a3s1hL5WYWfyY95NQZijbucFFCFHjM9bqgWUAfBLaeYGAFCWfqNpxKjzkr0PyRu6vs2DwT0lb7HHMYKBpi269up7pxCaJd3dGErG9OAV0nfdOjVSz/1vLEu4B8bzHfTydZTb4iwZneHo1itu010NU9ikwhQHO53cGfPtNwfH9w+Hz55vf//J8fzo+/+aNF9DU8qENZDRqKy9/eI1E1JXvUgw3BxYQcT25gdYVDTCyc1NnI0Fuct9JArqg7FVlcFBC9HYrCrpTbs53KJTngACAsYY0kAFEeV0X/hUhEcQpqWm+bREiLI4EQwgBkWmgapV/c8RDLtqxlsmu3njHFPhucdcUDpzLYrFgcTwDg5am8qClngshuFO4WE0avStqyMJ4G9clf4VICIimME5DHxLnD+mWujfLd2ccGp2HN68xwH2FRRyMZQBH8u2ZKEWaTvJ9rff4qRI4rqR1vS8O6wCuYj9IYb64wGFgt3Zd6lcuCk+l/1e9SioaY6qqVycR9YAflCvJRHHSGDYdWF3PONkaY9NcPRtnwEw9jucdEDeR3yWL1x2QWM0eHUqmuaEPQuy62F9ByekMP1+Jvd+P+jKU5/6cnDHY0RMEgnev/j6gevtAJwPE9i+CBIioBrhXty+5S0EZZ3UZGumDat32F75paSOsSeoJio4ae/vhw38A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Country = _t, #"30/09/2013" = _t, #"31/10/2013" = _t, #"30/11/2013" = _t, #"31/12/2013" = _t, #"31/01/2014" = _t, #"28/02/2014" = _t, #"31/03/2014" = _t, #"30/04/2014" = _t, #"31/05/2014" = _t, #"30/06/2014" = _t, #"31/07/2014" = _t, #"31/08/2014" = _t, #"30/09/2014" = _t, #"31/10/2014" = _t, #"30/11/2014" = _t, #"31/12/2014" = _t, #"Grand Total" = _t]),
    Custom1 = let 
tbl = Source, //name of the previous applied step
NextHeader = List.PositionOf(tbl[Segment], "Segment") //tbl[Segment] --access the first column as a list then find the position of "segment" other than the header
in
Table.FirstN(tbl, NextHeader)
in
    Custom1

[PhilipTreacy]

@M_SBS_6 

How to get your question answered quickly.

[Ashish_Mathur]

Hi,

This may be possible.  Share the download link of an MS Excel file with some dummy data there.

[Idrissshatila]

Hello @M_SBS_6 ,

I suggest if you can seperate this table into another sheet and work clean from the source this would be much better, cleaner and simple.

and with excel its easy to do that.

[Zanqueta]

Hi @M_SBS_6,

Unfurtunelly we don´t have a sample data to teste, but please try it:

1. Load the entire sheet (not just a single table).
2. In the Power Query editor:
   • Identify the column where the value "test errors" appears.
   • Apply logic to keep all rows from that point onwards and exclude the rest.
   • This can be done using an index and conditional filtering

Input Steps

# Add an index column
= Table.AddIndexColumn(PreviousStep, "Index", 0, 1, Int64.Type)

# Find the position of the row where Column1 = "test errors":
Position = Table.PositionOf(PreviousStep, [Column1 = "test errors"])

# Skip all rows above that position:
= Table.Skip(PreviousStep, Position)

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.