Search code examples
excelpowerqueryssas-tabulardmv

Excel Power Query truncating text field to 1024 characters

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.

Solution

  • 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 has the additional specificity to convert a field of type Any to type Text. I tried to just 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.