Dynamic inclusion of datasets from an online source

[ebtk333]

Hi,

I am working on a report that extracts data automatically from excel files uploaded to a webpage. Once a new excel file was uploaded to a specific webpage of my interest, I refresh the report via Power Bi Desktop and info included in the new excel file is incorporated together with old excel files that were previously uploaded to that webpage and then incorporated in the power bi report. This is possible because excel files are uploaded to a table in a webpage that power bi can catch.

Currently I am using the below formula to remove top 2 rows as they are not of interest and table starts in row 3.

(table) =>

let

    Source = table,

    #"Removed Top Rows" = Table.Skip(Source,2),

 Row 1 has text and row 2 is blank, in row 3 usually starts the table’s header.

Now this month there was an extra row hidden, whereby row 1 has text, rows 2&3 are blank and row 4 contains the table’s header instead of row 3.

Would anyone know how to overcome introduce condition to this formula, which would ask to skip first 2 rows if row 3 has text and skip first 3 rows if 3rd row is blank?

Thank you!

[v-qiuyu-msft]

Hi @ebtk333,

You can remove these blank rows firstly then remove the row 1 which has text:

table) =>

let

    Source = table,

    #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

    #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",1),

Best Regards,
Qiuyun Yu