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
v-xiaoyan-msft
on:
Dashboards
on:
100% stacked column chart - Detail label font colo...