Get only the data body (ignore total row) of structured table into Power Query

[freelensia]

Hi,

I have a structured table with Total Row turned on like this:

I tried to get its data body into Power Query. I tried this:

= Excel.CurrentWorkbook(){[Name="TransTbl"]}[Content]

And it got the Total row too.

I tried this but got error.

= Excel.CurrentWorkbook(){[Name="TransTbl[#Data]"]}[Content]

Edit:

I know we can remove the bottom row but that approach is not versatile. Sometimes this table would have the Total Row and sometimes not. I want to get just the data body, not get everything and remove the parts unwanted.

Anybody can help? Thanks so much.

[sumanpuvvala]

It took me a half hour to figure out that my reports were showing wrong data because Totals was enabled on the data source. This is a bug. Would be very helpful if someone knows how to get only the Data content of the table!

[mahoneypat]

Make your second step a Filter step where Ticker <> "Total".  Does that work?

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[freelensia]

Thanks @mahoneypat but as I mentioned, I do not want to get the entire table then filter out the rows i don't want. Because this will be used in a function where sometimes the input table will have a total row and sometimes not.

[mahoneypat]

I understand you don't want to remove last row but filtering out the word total should have no impact when it is absent. Can you clarify?

Regards,

Pat

[freelensia]

Because there maybe a value with "Total".

As I said I want to use this as a function and I need it to be versatile.

And I think it is ridiculous that there is no native way to import only the body of a structured table. Surely Microsoft People thought of this??

[mahoneypat]

Looking at your initial table, you could filter out the rows where Ticker = "Total" and Last Date is null (or one of other columns that don't have content in the Total row).  Also, I suggest you post your proposed functionality as an idea.  They implement the most popular ones.  Until then, you do have some options to proceed with your function I think.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[freelensia]

Sorry guys, none of this meets my requirement. I said I want to get only the data body, not get everything then exclude the total row using a second step. 

So I will wait for another answer that can do what I need natively.