Importing Excel data with multiple tables and sections

[gsksarepta]

I am working with an Excel sheet which has multiple sections below a table that I need to work with. On PowerBI, I only need to read the data from the table located on top and ignore everything after the latest row of the top table. Detailed in the picture below. Are there any transformations I can do in Power BI import-->Transformations to do this ? Thanks

[danextian · Accepted solution]

Hi @gsksarepta ,

I'd import the worksheet in PQ and find the position of the cell with Symbol/Trend text string. I would then keep the top rows based on the cell position -1. Remove the blanks from the date column and apply other transformations after.
Please see sample M-script and screenshots below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjpVgdINcIlWuMyjVB5Zqics1QueaoXAtUriUK19AAlYvqKkNUVxmiugobgaYkuDI3KT9HP6QoNS9FKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    Position = List.PositionOf(#"Changed Type"[Date],"Symbol/Trend")-1,
    Custom1 = #"Changed Type",
    #"Kept First Rows" = Table.FirstN(Custom1,Position),
    #"Filtered Rows" = Table.SelectRows(#"Kept First Rows", each [Date] <> null and [Date] <> "")
in
    #"Filtered Rows"

[halfglassdarkly]

Ideally format the data range you want to import as a table in excel as it sounds like it is currently just a range not a table, then just import the table in Power Query rather than the worksheet. You'd need to check this doesn't impact the other data etc. below though, so save a backup first!

Alternatively, based on your screenshot, if you expect no further date values in column A below row 24, one method you could try:

• Importing the worksheet in Power Query and promoting headers
• Setting the first column to date format which should error for non date values
• Remove errors
• Remove blanks
• Remove last 1 row (to exclude the date from row 24)

[gsksarepta]

@halfglassdarkly  thanks for the reply. The alternative solution doesn't work as the table gets updated with new row every week. The table range idea is good but I prefer not to do this as this excel file is maintained by few managers who like to keep it that way. Are there any options in power bi query editor for this situation? Thanks