Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
nilPower2018
New Member

Custom Function works in Query Editor but fails to apply changes to data model

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.

 

 

1 REPLY 1
v-yuta-msft
Community Support
Community Support

Hi nilPower2018,

 

First please confirm which query has error, you can delete some of the queries and then try again.

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.