Importing Multiple Tables contained within one Excel Spreadsheet

[Rehman2007]

Hello,

I have multiple tables (and matrixes) contained inside one spreadsheet of an Excel file and wish to import them into Power BI. What is the best method of importing them without having to apply the tabulation shortcut of CTRL + T on each individual table and matrix ? Would there be any specific issue of importing the matrixes ?

Regards

[Rehman2007]

Thanks, Xiaoxin. Is M language required as a solution for this ?

[v-shex-msft]

Hi @Rehman2007,

Yes, I mean use power query function to extract specific field values to instead raw steps.

If you are confused about coding formula, please share some dummy data with a similar data structure and expected results to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Rehman2007,

It is possible to extract specific cell fields from one excel sheet (we did not so recommend this, they may cause the issue and broken your tables every time the main sheet structure changes)

To extract specific fields, you can import the raw sheet into power bi as a query table. Then you can create new query tables that reference from the raw table and use Table.SelectColumns, Table.FirstN, Table.LastN function to choose specific ranges.

Table.SelectColumns - PowerQuery M | Microsoft Docs

Table.FirstN - PowerQuery M | Microsoft Docs

Table.LastN - PowerQuery M | Microsoft Docs

Regards,

Xiaoxin Sheng