Foxed by error refreshing is server, but not in desktop related to rebuilding queries

[masplin]

Hi

This model refreshes abslutely fine in desktop, but onpublishing get this error.   I thoguht you avoided this by staging the queries before merging which is what I think I have done. 

Data source error:	{"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"[Unable to combine data] Section1/StatusLog/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}}],"exceptionCulprit":1}}} Table: StatusLog.
Cluster URI:	WABI-UK-SOUTH-redirect.analysis.windows.net
Activity ID:	5392e0cd-e26d-40e2-8846-9209f5d700b8
Request ID:	3149688b-d448-f492-4573-5052ea2dbc59
Time:	2020-09-05 14:15:23Z

This is Query code so bringing in phoneUser data from a staging query that gets that data form the database.

let
    Source = Sql.Database("ABC", "softphone", [Query="
    SQL Query

    "]),
    #"Extracted Date" = Table.TransformColumns(Source,{{"StatusDate", DateTime.Date, type date}}),
    #"Inserted Date" = Table.AddColumn(#"Extracted Date", "Date", each Date.From([StatusEndDT]), type date),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"StatusDate", "StatusStartDate"}, {"Date", "StatusEndDate"}}),
    #"Extracted Time" = Table.TransformColumns(#"Renamed Columns",{{"StatusStartDT", DateTime.Time, type time}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Extracted Time",{{"StatusEndDT", "StatusEndTime"}}),
    #"Extracted Time1" = Table.TransformColumns(#"Renamed Columns2",{{"StatusEndTime", DateTime.Time, type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Time1",{{"StatusStartDT", "StatusStartTime"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Id", "PhoneUserId", "StatusStartDate", "StatusStartTime", "StatusEndDate","StatusEndTime", "Status"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"StatusStartDate", Order.Ascending}, {"StatusStartTime", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"PhoneUserId"}, PhoneUser, {"Id"}, "PhoneUser", JoinKind.LeftOuter),
    #"Expanded PhoneUser" = Table.ExpandTableColumn(#"Merged Queries", "PhoneUser", {"AdviserId"}, {"Adviser ID"})
in
    #"Expanded PhoneUser"

Can someone tell me what is wrong with this? 

Thanks 

Mike

[Greg_Deckler]

@masplin - Very difficult to troubleshoot these kinds of things. @ImkeF and @edhans might be able to tell what is going on but you might have better luck checking the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[masplin]

Hi Greg

Spent 2 weeks gettinmg the modle working and now cant get it to refresh!!!

I have pro account so wil ltry that. I stripped out the merge in this query which got rid of that error , but replaced with an evenmore nonsense simialr error!!!  i havent had issues before with something working in desktop but not on theservice so must be quite obscure.

Thanks

[masplin · Accepted solution]

So if anyone else gets this error it is becuase I didn't stage my queries fully.  This was the instruction from support whoich has fixed it. Pity that this error isn't apparennt when you are building in desktop nor is the error message terribly helpful. 

The error is due to reference to external query – “PhoneUser” in a query that access external data. We cannot have the query that directly accesses a data source also have a reference to external query.

To remediate this, try the steps below:

1. Create a blank query and name the query as StatusLogList
2. Add the following statement to the query:

let

    Source = Sql.Database("ABC", "softphone", [Query="SQL Query"]),

In

    Source

1. Update the existing query for StatusLog as follows:

let    Source = StatusLogList    #"Extracted Date" = Table.TransformColumns(Source,{{"StatusDate", DateTime.Date, type date}}),    #"Inserted Date" = Table.AddColumn(#"Extracted Date", "Date", each Date.From([StatusEndDT]), type date),    #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"StatusDate", "StatusStartDate"}, {"Date", "StatusEndDate"}}),    #"Extracted Time" = Table.TransformColumns(#"Renamed Columns",{{"StatusStartDT", DateTime.Time, type time}}),    #"Renamed Columns2" = Table.RenameColumns(#"Extracted Time",{{"StatusEndDT", "StatusEndTime"}}),    #"Extracted Time1" = Table.TransformColumns(#"Renamed Columns2",{{"StatusEndTime", DateTime.Time, type time}}),    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Time1",{{"StatusStartDT", "StatusStartTime"}}),    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Id", "PhoneUserId", "StatusStartDate", "StatusStartTime", "StatusEndDate","StatusEndTime", "Status"}),    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"StatusStartDate", Order.Ascending}, {"StatusStartTime", Order.Ascending}}),    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"PhoneUserId"}, PhoneUser, {"Id"}, "PhoneUser", JoinKind.LeftOuter),    #"Expanded PhoneUser" = Table.ExpandTableColumn(#"Merged Queries", "PhoneUser", {"AdviserId"}, {"Adviser ID"})in    #"Expanded PhoneUser"