Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gsksarepta
Helper II
Helper II

Importing Excel data with multiple tables and sections

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

gsksarepta_0-1679788639845.png

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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:

danextian_0-1679814495489.pngdanextian_1-1679814510284.png

 

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"

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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:

danextian_0-1679814495489.pngdanextian_1-1679814510284.png

 

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"

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
halfglassdarkly
Resolver IV
Resolver IV

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)

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.