Forum Discussion

gsksarepta's avatar
gsksarepta
Helper II
3 years ago
Solved

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...
Need Help
Tips and Tricks
  • danextian's avatar
    danextian
    3 years ago

    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"

     

danextian's avatar
danextian
Super User
3 years ago

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"