Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
|
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
Solved! Go to 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:
let
Source = Sql.Database("ABC", "softphone", [Query="SQL Query"]),
In
Source
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"
@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".
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
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:
let
Source = Sql.Database("ABC", "softphone", [Query="SQL Query"]),
In
Source
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.