Tabular Model not loading data from M (Power Query) Function in server

Franco Iranzi 21 Reputation points
2022-04-07T13:14:02.477+00:00

Hello!

I've been trying to threat a field using M functions, but, for some reason it just doesn't returns any values at the CUBE on the server.

If I try to run it using my machine in Visual Studio as working directory the data loads normally, but when I do deploy it on the server it does not for some reason.

The cube full process runs with success and there's no error messages at all, I've tried to drop the cube and redeploy it but nothing change

I think there's some package missing on the server side to support functions like Html.Table, I've tried other custom functions avoiding that one but none of then worked.

Cube compatibility Level: 1500

M query example:

let
Source = Value.NativeQuery(#"MyServer;MyDB", "
select * from MY_QUERY
"),
Custom1 = Table.ReplaceValue(Source,"#(cr)#(lf)","",Replacer.ReplaceText,{"Department"}),
RemoveTags = Table.AddColumn(#"Custom1", "Custom", each try Html.Table([#"Risk Comment"], { {"Column1", ":root"}}){0}[Column1] otherwise null),
#"Cleaned Text" = Table.TransformColumns(RemoveTags,{{"Custom", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Custom", Text.Trim, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Risk Comment"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","-","",Replacer.ReplaceText,{"Custom"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Risk Comment", each Text.Start([Custom], 255))
in
#"Added Custom"

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,258 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,296 Reputation points
    2022-04-08T01:24:57.683+00:00

    Hi @Franco Iranzi ,

    I think there's some package missing on the server side to support functions like Html.Table, I've tried other custom functions avoiding that one but none of then worked.

    Please check if your data source is supported by SSAS, you may refer to below official document

    data-sources-supported-ssas-tabular-1400

    By the way, your sample M query seems correct.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.