Create 3 different tables from a single sheet in excel

[Anonymous]

Hello Power BI experts, 

I have a summary report that has data from three from different years, lets say 2018, 2019 and 2020.

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.

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 

2018	A	B	C	D
Revenue	1	10	21	10

2019	E	F	G	H	I
Revenue	1	10	21	10	1

2020	J	K
Revenue	$                1	$                   1

[V-pazhen-msft · Accepted solution]

@Anonymous 

In order to optimize the performance, instead of create calculated table using DAX, I would recommend you to transform the query in Query Editor to create the 3 tables.

The idea is first create an index column and make another 2 copies of the table. On each table you can filter the index column with <6, >6 and <13, >13 respectively. Then promote headers and remove null columns.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please share a link to the excel sheet with your example data to see how they are spaced out.  Since you are getting the file automatically, I expect you can't convert them to Excel tables to make them easier to bring into the query.  Also, you should consider unpivoting your three tables and appending them to one table to make your analysis and visualization easier.

Regards,

Pat

[Anonymous]

Thanks for the reply Pat, I cannot really unpivot the data and make a combined since they have different column headers etc. What I am currently thinking is to create a calculated table and write a DAX that will always fetch the rows, say 1-6 and make a separate table, then fetch the rows 7-12 for the second table and so on. Is there an easier approach you can recommend? Below is the link to my sample data. 

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