Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
freelensia
Advocate II
Advocate II

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

Hi,

 

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

 
 

].png

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.

 

7 REPLIES 7
sumanpuvvala
New Member

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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??

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors