Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.7k views
in Technique[技术] by (71.8m points)

powerbi - Create three tables from a single excel sheet

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...