Create three tables from a single excel sheet

Question

I have a summary report that has data from three from different years, lets say 2018, 2019 and 2020- Below is a shared link to sample data from excel

The below three tables are in the same sheet of excel. Also, note that these column names are different and the tables have different sizes. I want to have all these tables as separate tables in one Power BI report.

https://docs.google.com/spreadsheets/d/1WQ6n6FKM5ZjAnndcfY9xCoY-1IaTN5dAnckT0IXzz7E/edit?usp=sharing

Is there an efficient way to import tables into my report, I can write an M code to separate the tables out and extract them as three different tables. Also, one important point is that these reports are created from a portal and I get these reports everyday and that I will connect with Power BI. In these automated reports, the tables are also not named.

Can anyone help with this? Thank you so much

Are the number of rows in each table fixed or variable? How about the columns; fixed or variable? Are there always exactly three tables. — Alexis Olson, Jan 12 '21 at 20:24

score 0 · Answer 1 · answered Jan 12 '21 at 20:56

I don't think you can create a dynamic number of queries but you can load all three tables into a list of tables like this:

let
    Sheet1 = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/1WQ6n6FKM5ZjAnndcfY9xCoY-1IaTN5dAnckT0IXzz7E/export?format=csv")),
    BlankRows = List.PositionOf(Sheet1[Column1], "", 2),
    FirstBreak = BlankRows{0},
    SecondBreak = BlankRows{1},
    Table1 = TrimAndPromote(Table.Range(Sheet1, 0, FirstBreak)),
    Table2 = TrimAndPromote(Table.Range(Sheet1, FirstBreak + 1, SecondBreak - FirstBreak - 1)),
    Table3 = TrimAndPromote(Table.Range(Sheet1, SecondBreak + 1))
in
    {Table1, Table2, Table3}

Where TrimAndPromote is a separate custom function I've written as a separate query:

(T as table) as table =>
let
    
    TrimBlankCols = List.Select(Table.ToColumns(T), each not List.ContainsAll({null,""}, _)),
    PromoteHeaders = Table.PromoteHeaders(Table.FromColumns(TrimBlankCols))
in
    PromoteHeaders

My assumption here is that you have exactly three tables to load but each table could have any (positive) number of rows or columns. A dynamic number of tables would get more complicated.

Create three tables from a single excel sheet

1 Answers1