cancel
Showing results for 
Search instead for 
Did you mean: 

Web API data sources (Adaptive Insights) refresh in PBI Desktop but not in PBI web service

I have 7 web api data sources in my pbix file.  I am able to refresh in PBI Desktop (takes about 20 mins) but not in PBI web service.  I have added some code that prior to adding them, I was also not able to refresh in PBI Desktop.  It was timing out.  I'm trying to understand why I'm able to refresh in the PBI desktop but not on the web.  What am I missing?  Below is the M code I'm using in one of the queries.  They are all pretty much the same structure.  Just different parameters.  To summarize, the added Timeout and sessionTimeout codes made the refresh work for PBI desktop but not PBI web service.

 

let
    Source = Xml.Tables(
        Web.Contents("https://api.adaptiveinsights.com/api/v14",
            [Timeout=#duration(0, 2, 0, 0), Content = Binary.Buffer(Text.ToBinary("<?xml version='1.0' encoding='UTF-8'?>
                <call method=""exportData"" callerName=""PowerBI"">
                    <credentials login=""MyUserName"" password=""MyPassword"" instanceCode=""MyInstance"" sessionTimeout=""120""/>
                    <version name=""Forecast""/>
                    <format useInternalCodes=""true"" includeUnmappedItems=""false""/>
                    <filters>
                        <accounts>
                            <account code=""Expenses"" isAssumption=""false"" includeDescendants=""true""/>
                            <account code=""Non_Operating_Expenses"" isAssumption=""false"" includeDescendants=""true""/>
                        </accounts>
                        <timeSpan start=""Jan-2018"" end=""Dec-2019""/>
                    </filters>
                   
                    <dimensions>
                        <dimension name=""Vendor""/>
                        <dimension name=""Project Allocation""/>
                    </dimensions>
                    <rules includeZeroRows=""false"" includeRollups=""false"" markInvalidValues=""false"" markBlanks=""false"" timeRollups=""false"">
                        <currency useCorporate=""false"" useLocal=""false"" override=""USD""/>
                    </rules>
                </call>"))
            ])),
    CSV = Table.SelectColumns(Source,{"output"}),
    #"Split Column by Delimiter (Rows)" = Table.ExpandListColumn(Table.TransformColumns(CSV, {{"output", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "output"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter (Rows)", "output", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"output.1", "output.2", "output.3", "output.4", "output.5", "output.6", "output.7", "output.8", "output.9", "output.10", "output.11", "output.12", "output.13", "output.14", "output.15", "output.16", "output.17", "output.18", "output.19", "output.20", "output.21", "output.22", "output.23", "output.24", "output.25", "output.26", "output.27", "output.28", "output.29", "output.30", "output.31"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter1", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Account Name", "Account Code", "Level Name", "Vendor", "Project Allocation", "Jan-2018", "Feb-2018", "Mar-2018", "Apr-2018", "May-2018", "Jun-2018", "Jul-2018", "Aug-2018", "Sep-2018", "Oct-2018", "Nov-2018", "Dec-2018", "Jan-2019", "Feb-2019", "Mar-2019", "Apr-2019", "May-2019", "Jun-2019", "Jul-2019", "Aug-2019", "Sep-2019", "Oct-2019", "Nov-2019", "Dec-2019"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Account Name", "Account Code", "Level Name", "Vendor","Project Allocation"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Period"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [Value] <> "0.0"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Value", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Period", type date}, {"Value", type number}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Changed Type",{{"Period", Date.EndOfMonth, type date}}),
    #"Added Account Column" = Table.AddColumn(#"Calculated End of Month", "Account", each Text.Start([Account Code], 5), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Account Column",{{"Account", Int64.Type}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Account"}),
    #"Added Dept Column" = Table.AddColumn(#"Removed Errors", "Dept", each Text.Start([Level Name], 6), type text),
    #"Added Version Column" = Table.AddColumn(#"Added Dept Column", "Version", each "ACT/FCST"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Version Column",{{"Account", type text}, {"Version", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","Vendor","",Replacer.ReplaceValue,{"Vendor"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Project Allocation","",Replacer.ReplaceValue,{"Project Allocation"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value1",{"Account Name", "Account Code", "Level Name", "Project Allocation", "Period", "Value", "Account", "Dept", "Version", "Vendor"})
in
    #"Reordered Columns"
 
 
Best regards,
 

 

Ferdinand

Status: New
Comments
Moderator

Hi @ferdsjoseph,

 

On Power BI service, please go to Refresh History, find the failure record, click on Show button to find detail error information, please share them with us. 

 

Best Regards,
Qiuyun Yu 

Regular Visitor

Hi Qiuyun,

 

I have been testing minor modifications to see if anything makes it work but no success.  Here's the usual error message detail.

 

Error Details.png

Moderator

Hi @ferdsjoseph,

 

Please try to manually refresh the dataset again. If the same issue still occurs, I would suggest you create a support ticket to get dedicated support. 

 

Support Ticket.gif

 

 

Best Regards,
Qiuyun Yu 

Regular Visitor

Thanks Qiuyun! I now have a support ticket number: 119012619594807.

 

 

Best regards,

 

Ferdinand

Moderator

Hi @ferdsjoseph.,

 

Please collaborate with engineers to resolve the issue. It would be better if you could share the solution here once the issue is resolved, which will benefit other members who have the same issue. Smiley Happy

 

Best Regards,
Qiuyun Yu 

Regular Visitor

Will do. I have a scheduled call with support tomorrow. Will post solution here when the issue is resolved.

 

 

Best regards,

 

Ferdinand

Idea Statuses