I have a query that returns: "Formula.Firewall: Query 'msciData' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
It works fine with privacy levels set to ignore. And this only happens with the privacy levels for all sources switched to "Organisational", and the main setting set to "Combine privacy levels across data sources". Even though I know I can just switch these privacy levels to ignore so it works fine in desktop mode, I'm being told by an engineer that privacy levels cannot be ignored when running a scheduled refresh of the dataset within the online Power BI data service. (Does everyone agree with that claim?)
What am I doing wrong with the following code for it to return the above? It's telling me to rebuild my data combination but I very often use this approach of building a custom function to loop a several strings into parameter through a URL.
My custom function is:
let
#"Table" = (date_column,index_style,index_size,index_market,index_scope) as table =>
let
Source = Csv.Document(Web.Contents("
https://app2.msci.com/products/", [RelativePath="service/index/indexmaster/description/indexes?calc_date="&date_column&"&index_variant=NETR¤cy_symbol=GBP&index_market="&index_market&"&index_scope="&index_scope&"&index_size="&index_size&"&index_style="&index_style&"&index_suite=C"]),[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MSCI Index", type text}, {"Index Code", Int64.Type}, {"Last", type text}, {"Day", Percentage.Type}, {"MTD", Percentage.Type}, {"3MTD", Percentage.Type}, {"YTD", Percentage.Type}, {"1 Yr", Percentage.Type}, {"3 Yr", Percentage.Type}, {"5 Yr", Percentage.Type}, {"10 Yr", type text}})
in
#"Changed Type"
in
#"Table"
And the main query is:
let
market = Source[market],
#"Converted to Table" = Table.FromList(market, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"}),
index_market = Table.SelectRows(#"Expanded Column1", each ([id] <> "China Markets" and [id] <> "GCC and Arabian Markets"))[id],
Custom1 = Source,
size = Custom1[size],
#"Converted to Table1" = Table.FromList(size, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "name"}, {"id", "name"}),
index_size = Table.SelectRows(#"Expanded Column2", each ([name] = "Large Cap" or [name] = "Micro Cap" or [name] = "Mid Cap" or [name] = "Small Cap" or [name] = "Standard (Large+Mid Cap)"))[id],
Custom2 = Source,
style = Custom2[style], #"Converted to Table2" = Table.FromList(style, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
index_style = Table.ExpandRecordColumn(#"Converted to Table2", "Column1", {"id", "name"}, {"id", "name"})[id], Custom3 = {"Region","Country"},
#"Converted to Table3" = Table.FromList(Custom3, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table3", "IndexMarket", each index_market),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "IndexMarket"),
Custom4 = Table.AddColumn(#"Expanded Custom", "IndexSize", each index_size),
#"Expanded IndexSize" = Table.ExpandListColumn(Custom4, "IndexSize"),
Custom5 = Table.AddColumn(#"Expanded IndexSize", "IndexStyle", each index_style),
#"Expanded IndexStyle" = Table.ExpandListColumn(Custom5, "IndexStyle"),
#"Added Custom1" = Table.AddColumn(#"Expanded IndexStyle", "Date", each fundReturnDate),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Column1", "IndexMarket", "IndexSize", "IndexStyle"}, msciToExclude, {"Column1", "IndexMarket", "IndexSize", "IndexStyle"}, "msciToExclude", JoinKind.LeftOuter),
#"Expanded msciToExclude" = Table.ExpandTableColumn(#"Merged Queries", "msciToExclude", {"Column1"}, {"Column1.1"}), #"Filtered Rows1" = Table.SelectRows(#"Expanded msciToExclude", each ([Column1.1] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Column1.1"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "data", each msciPull([Date], [IndexStyle], [IndexSize], [IndexMarket], [Column1])),
#"Expanded data" = Table.ExpandTableColumn(#"Invoked Custom Function", "data", {"MSCI Index", "Index Code", "Last", "Day", "MTD", "3MTD", "YTD", "1 Yr", "3 Yr", "5 Yr", "10 Yr"}, {"MSCI Index", "Index Code", "Last", "Day", "MTD", "3MTD", "YTD", "1 Yr", "3 Yr", "5 Yr", "10 Yr"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded data", each ([MSCI Index] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"MTD", Percentage.Type}, {"3MTD", Percentage.Type}, {"YTD", Percentage.Type}, {"1 Yr", Percentage.Type}, {"3 Yr", Percentage.Type}, {"5 Yr", Percentage.Type}, {"10 Yr", Percentage.Type}}) in #"Changed Type"
