Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
Please forgive me for the long post, but I continuously encounter this "one of the loaded queries contained error" problem only when try to apply the changes from the Query editor into data model. All the queries work without any error in the Query Editor.
Clicking on the error displays an Empty table without any error.
The problem is explained in detail bellow:
I need to update values in multiple table columns based on values in other columns. I achieve this as follow:
1) Add a new column - Add a new column with custom function to set the values based on values in other columns and some conditions.
2) Delete the Existing column in which values needed to be updated based on values from other columns
3) Now rename the new column with name of deleted column.
Pleaes note that the above method works (as it should). The problem is when I try to achieve the above via custom function, so I can call that single custom function whenerver I need to transfrom the values in any Column.
I created a "concateEachColumnFromList", "addColumnByCustomFunction" and "transformColumnByCustomFunction" as follow:
Function 1: "concateEachColumnFromList"
```
let
concateEachColumnFromList = (columnNamesList as list) as text =>
let
concateItem = (state,current) =>
let
currentItem = if current = null or current = "null" or current = "" then current else "_[" & current & "]",
concated = if state = "" then state & currentItem else state & "," & currentItem
in
concated,
eachRowColumnList = List.Accumulate(columnNamesList, "", concateItem)
in
eachRowColumnList
in
concateEachColumnFromList
```
Function 2: "addColumnByCustomFunction"
```
let
addColumnByCustomFunction = (srcTable as table, newColumnName as text, functionName as text,
optional columnNamesList as nullable list, optional returnType as nullable text) as any =>
let
valType = if returnType <> null and Text.Contains(returnType, "type") then Expression.Evaluate(returnType) else type any,
columnList = if List.Count(columnNamesList) > 0 then concateEachColumnFromList(columnNamesList) else null,
fnExpression = functionName & "(" & columnList & ")",
ColumnCreator = (_) =>
let
invoked = Expression.Evaluate(fnExpression, #shared & [functionName=functionName, _=_])
in
invoked,
AddColumn = Table.AddColumn(srcTable, newColumnName, ColumnCreator, valType)
in
AddColumn
in
addColumnByCustomFunction
```
Function 3: "transformColumnByCustomFunction"
```
let
transformColumnByCustomFunction = (srctable as table, transformColName as text, functionName as text,
optional columnNamesList as nullable list, optional returnType as nullable text) as any =>
let
newTempColName = "temp" & Text.From(Number.Random()) & transformColName,
#"Added Temp Column" = addColumnByCustomFunction(srctable, newTempColName, functionName, columnNamesList, returnType),
#"Removed old Column" = Table.RemoveColumns(#"Added Temp Column",{transformColName}),
#"Renamed new Column" = Table.RenameColumns(#"Removed old Column",{{newTempColName, transformColName}})
in
#"Renamed new Column"
in
transformColumnByCustomFunction
```
Now I can use the "transformColumnByCustomFunction" to set the values based on values in other columns and specific conditions as defined in custom functions . For example:
#"Update AssessedRating Column" = trnasformColumnByCustomFunction(#"Changed Type", "AssessedRating", "SetRating", {"AssessedRating", "InitialRating"}, "type text")
The above query works fine and I get the desired result. The Problem is when I click on "SAVE" button in Query Editor, it fails stating "one of the loaded queries contained error". When I click on error it displays an empty table with no Error details.
Please advise.
Hi nilPower2018,
First please confirm which query has error, you can delete some of the queries and then try again.
Regards,
Jimmy Tao
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |