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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PBI-Learner
New Member

Power BI Export to Excel: Data with current layout is unavailable for the Last drilled down field

Hi all,

 

My client usually export data through using "Data with current layout." The function used to work well, but now she met a weried problem. She can't export if she drill down all the fields , but she can do it if she doesn't drill down to the last field.

 

PBILearner_1-1659377864019.png

 

The error message looks like

PBILearner_3-1659379101953.png

 

The report is a matrix and it can be drilled down to the field LandedCost. 

PBILearner_0-1659377752968.png

 

If assuming to drill down to FOBCost, a field before LandedCost, the "Data with current layout." functionality runs well. 

 

See Build visual in  Desktop. No columns in the PBI Desktop. LandedCost is in the rows.

 

PBILearner_2-1659378108031.png

 

Besides this problem, she can export the data through the summarized data and underlying data as well. 

 

Another investigation I did is that I checked the official documentation https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=powerbi-de...

 

This report includes 7,800 rows records in the tabular format. That is far less than the limitation 150,000 rows in xlsx.file or 50,000 rows from matrix visuals.  According to the documentation, this is not supposed to be the reason.

 

I saw many requests regarding this type of problem, but they all refer that Data with current layout doesn't work at all. But my export just partially works. That is so strange.

 

The solution. I walk around this issue by changing LandedCost from Rows to Values in Desktop. Now it is OK. But my client said she could export even drilled down to LandedCost before. I feel so confused. 

 

Thank anyone who can help out.

 

 

1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community Support

Hi @AnamikaK @XtraTerrestrial ,

 

This issue has been confirmed as a known issue internally.  Internal ICM ID: 324897008.

 

Please be patient to wait for fixing. If there is any news, I will update it here.

View solution in original post

6 REPLIES 6
v-yadongf-msft
Community Support
Community Support

Hi @AnamikaK @XtraTerrestrial ,

 

This issue has been confirmed as a known issue internally.  Internal ICM ID: 324897008.

 

Please be patient to wait for fixing. If there is any news, I will update it here.

XtraTerrestrial
Regular Visitor

Hi, I have the same problem - there seems to be a limit of 13 Rows?

 

I’ve set up a test scenario as follows:

XtraTerrestrial_0-1660227847603.png

 

 

Then exporting “Data with current layout”:

XtraTerrestrial_1-1660227847607.png

 

 

Which results in the following error:

XtraTerrestrial_2-1660227847608.png

 

 

Whereas this setup exports “Data with current layout” successfully?

 

XtraTerrestrial_3-1660227847611.png

 

 

The only difference is the number of Columns displayed.

Q: Is there currently a limit of 13 Row-Columns displayed for the “Data with current layout” export to work?

Q: Can this limit be increased?

 

Thanks, regards

ET.

AnamikaK
Frequent Visitor

Hi @v-yadongf-msft,

We are facing similar issue.
When we are selecting a very small subgroup of say max 20 rows in the extract then still we observe the same error. 

The main reason is when the rows in the matrix is 13 or less the export runs fine. As soon as we add the 14th row tot he matrix we get the error in the export.

 

This was working earlier for all our visuals and the users were able to export data. Now we are getting a lot of feedback that this has stopped working. Would be great to hear back on this 🙂 . Thanks

Anonymous
Not applicable

The problem is that when "Expand all down one level in the hierarchy" exceeds a certain number, an export error occurs. it might be.

v-yadongf-msft
Community Support
Community Support

Hi @PBI-Learner ,

 

Based on your description, I created a matrix similar to yours with 7800 rows.

vyadongfmsft_0-1659605390719.png

 

In my test, it exports fine as data with current lay out.

vyadongfmsft_1-1659605390721.png

 

Here are some suggestions for you to answer your confusion:

 

1.Are you using directquery mode? When you're using DirectQuery, the maximum amount of data that Power BI can export is 16-MB uncompressed data. An unintended result may be that you export less than the maximum number of rows of 150,000. As you said, your report includes 7,800 rows records in the tabular format, please check your data size.

 

2.Maybe there are too many rows. So when you changed LandedCost from Rows to Values, it worked.

 

3.For export from matrix visuals using Data with current layout, consider the following:

(1)Matrices with columns and/or values but no rows will be exported as having rows and/or values but no columns.

(2)Matrices with only one row and/or values but no columns will be exported as table (no right border separator).

(3)If the Show on rows toggle is set to 'On' in Power BI Desktop for a table or matrix visual, the visual format would not be preserved when data is exported to Excel.

(4)If the Row subtotals toggle is set to 'Off' in Power BI Desktop for a matrix visual, but the matrix visual has expanded and collapsed sections, exported data will contain subtotals for rows. To work around this issue, use the Expand | All command from the visual's context menu.

 

For more information, please refer to: Export data from a Power BI visualization - Power BI | Microsoft Docs

 

Best Regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank @v-yadongf-msft for your reply!

 

1. I am not using directquery mode. I am using SQL statement instead.

 

2. This might be the reason for the issue. I have 13 rows except for LandedCost, meaning I have total 14 rows to expand. Before I posted this request, I put all 14 rows in the report, and my client could download data with layout. Now I have to put the LandedCost into Values and this works. I doubt it might be row number's issue, but the weird thing is it worked before. I saw your posting "This issue has been confirmed as a known issue internally.  Internal ICM ID: 324897008." That's great but I also found another issue.

 

I put the 14th rows i.e. LandedCost from Values to Rows again on the PBI Desktop side.

PBILearner_1-1660917718099.png

 

The report can be drilled down to LandedCost correctly.

 

However, once I published it and moved to PBI Services side, I couldn't find LandedCost row.

Please notice the drill down icon . It still shows it is clickable. 

PBILearner_2-1660917806857.png

 

But in reality, after I clicked it, it doesn't show LandedCost though. 

 

Has MS been fixing the previous issue and eventually decides to set the number of rows to 13?

 

Thank everyone who assisted again!

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors
Top Kudoed Authors