1

I am accessing a SSAS DMV through Power Query in Excel via:

let
    Source = AnalysisServices.Database(TabularServerName, TabularDBName, 
             [Query="select * from $SYSTEM.TMSCHEMA_EXPRESSIONS"])
in
    Source

This works great in Power BI, but in Excel, the Expression column is limited to a max of 1024 characters. How do I get Power Query in Excel to give me the entire value? My largest values are around 15000 characters, so still within the stated limits of Power Query that I can find.

If I set up a table with a connection and query behind it, Excel can pull in the entire Expression column, but the downside is the server and database cannot be parameterized and have to be manually changed in the connection. Also I don't remember how to do this manually, so I always have to access the DMV from DAX Studio and export to Excel to set it up!

Update

I did some heavy transformations of this column. I parsed out a value, I used it to merge the file with itself and add a column that I then did a bunch of transformations on, and then used it to replace text within the original problem column. And something in that pulled in the whole value. I tried just doing small parts of this, like adding a column that referenced the problem column, or doing a replace in the problem column, and none of that worked.

So, no, not easy to duplicate or figure out which step fixed it, but for my purposes, I now have what I need.

TheRizza
  • 1,577
  • 1
  • 10
  • 23
  • 1
    There doesn't seem to be a problem if the long text field comes from an Excel cell. How are you verifying that there is a problem? (Obviously, I cannot pull anything from your server). – Ron Rosenfeld Dec 28 '21 at 02:31
  • Thanks for this. I check by adding a column with =LEN(@[Expression]). Nothing is over 1024. I did some transformations on this column and one of them caused Excel to pull in the whole result. I don't have time at the moment to figure out which one fixed it, but I'm off and running! – TheRizza Dec 28 '21 at 14:52
  • 1
    Glad you have it working. If you ever figure out the source of the problem, you can post it as an answer. – Ron Rosenfeld Dec 28 '21 at 17:40
  • I encountered this issue by concatenating many lines of text sourced from a list. The resulting values are truncated at 1024 characters. – CalvinDale Aug 21 '22 at 03:47

2 Answers2

1

I think it is related to the type of the column your are loading in Excel. I had the same issue and read your answer (with Table.ReplaceValue). Your solution is hiding the initial point : The function used in the expression you shared for Table.ReplaceValue() is Replacer.ReplaceText that as the additional specificity to convert a field of type Any to type Text. I tried to juste change the type of my field that was truncated when loaded in Excel, from type Any to type Text. Result : the complete values were then loaded in my worksheet.

Grosbeta
  • 26
  • 3
-1

I encountered a similar issue:

I loaded an Excel table containing large texts (around 2500 characters per cell) in power query, made some transformations, and wanted to load the result in another sheet.

All my text cells were truncated at 1024 characters. I made some debugging and noticed that the issue disappear if I untick the "Add this data to the Data Model" in the import date pop-up window.

When unticked, my cells are not truncated anymore and contain the complete text generated by the power query.

Hope this helps.

JamesB
  • 569
  • 1
  • 9
  • 31
Thierry GDL
  • 1
  • Changing the data type of the column from "any" to "text" was my fix, thanks to @Grosbeta's answer above. I did other transformations that caused a similar change, but the type setting was the real fix. – TheRizza Jun 09 '23 at 16:13
  • As a new StackOverflow user, please notice that this question already has an accepted answer and that the question is over a year old. Typically, you should only provide another answer if you have something to improve upon the accepted answer, such as it didn't work for you, or a new solution becomes available. In this case, changing the load to the Data Model is a pretty significant change, whereas a type change is a minor change and really something that should be done in the query (bug fix), so the original answer is better. – TheRizza Jun 09 '23 at 16:25