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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply

"Compressed Stream" error with Analyze in Excel

We are using the Analyze in Excel addin with a certain report, pulling the data from service.  This report as around 500000 records.  There are about 80 fields we need to add in a flattened pivot style to make one large table.  We are finding there seems to be a limit to the number of fields we can add, until the attached error occurs.  I am wondering if this is a limitation with using Pro licenses, or something else.  AnalyzeError.png

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Try Export to Excel with live connection and see if you get the same error.

View solution in original post

So this one is a bit beyond my DX skills (lack thereof).

I stripped it back so I can get the syntax down to just two columns:

 

DEFINE VAR __DS0Core =SUMMARIZE('WCO','WCO'[No.],'WCO'[MHI Claim Date]) VAR __DS0BodyLimited = TOPN(500000,__DS0Core,'WCO'[No.],1,'WCO'[MHI Claim Date])EVALUATE __DS0BodyLimited ORDER BY 'WCO'[No.], 'WCO'[MHI Claim Date]

 

I changed it to this:

DEFINE VAR __DS0Core =SELECTCOLUMNS(SUMMARIZE('WCO','WCO'[No.],'WCO'[MHI Claim Date]),'WCO'[No.],"No.",'WCO'[MHI Claim Date],"Claim Date") VAR __DS0BodyLimited = TOPN(500000,__DS0Core,'WCO'[No.],1,'WCO'[MHI Claim Date]) EVALUATE __DS0BodyLimited ORDER BY 'WCO'[No.], 'WCO'[MHI Claim Date]

 

But then I get

bruceandersonBI_0-1706857411857.png

Pretty sure I just have a peice of the syntax in the wrong spot.

 

View solution in original post

8 REPLIES 8

So this one is a bit beyond my DX skills (lack thereof).

I stripped it back so I can get the syntax down to just two columns:

 

DEFINE VAR __DS0Core =SUMMARIZE('WCO','WCO'[No.],'WCO'[MHI Claim Date]) VAR __DS0BodyLimited = TOPN(500000,__DS0Core,'WCO'[No.],1,'WCO'[MHI Claim Date])EVALUATE __DS0BodyLimited ORDER BY 'WCO'[No.], 'WCO'[MHI Claim Date]

 

I changed it to this:

DEFINE VAR __DS0Core =SELECTCOLUMNS(SUMMARIZE('WCO','WCO'[No.],'WCO'[MHI Claim Date]),'WCO'[No.],"No.",'WCO'[MHI Claim Date],"Claim Date") VAR __DS0BodyLimited = TOPN(500000,__DS0Core,'WCO'[No.],1,'WCO'[MHI Claim Date]) EVALUATE __DS0BodyLimited ORDER BY 'WCO'[No.], 'WCO'[MHI Claim Date]

 

But then I get

bruceandersonBI_0-1706857411857.png

Pretty sure I just have a peice of the syntax in the wrong spot.

 

you got the SELECTCOLUMNS syntax backwards.

 

EVALUATE SELECTCOLUMNS(SUMMARIZE ( 'WCO', 'WCO'[No.], 'WCO'[MHI Claim Date] ),"No.",[No.],"Claim Date",[MHI Claim Date])
lbendlin
Super User
Super User

In Excel, right click on one of the values then Table...Edit Query. The Command Text will have the DAX query that you can modify (among other things you can remove the silly 500K limit).  Encaplsulate the existing query with SELECTCOLUMNS and specify your desired colunm names.

Many thanks!  I have no error when exporting to live.  However, I see live option is not available  for "Underlying Data".  So I will need to add all of the fields into the BI report (80+) so they come out in the summarised option, hopefully.  I will try that. 

lbendlin
Super User
Super User

Try Export to Excel with live connection and see if you get the same error.

That does get around the issue.  Nice and fast, no error. Only issue which is a new one is the table name gets appended to all columns in the live connection.  I wonder if there is any way to prevent that.

 

bruceandersonBI_0-1706838975579.png

 

Yes, you can modify the DAX query via SELECTCOLUMNS.  But it will be quite tedious when you have so many columns.

If it will work, I would embrace the tedious.  Ihave searched aroud for that but not finding anything in my resources with DAX on how to use SELECTCOLUMNS in that way.  Would greatly appreciate if you can point to how/where to do that?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors