Forum Discussion

butterscotch's avatar
butterscotch
Helper II
2 years ago

cannot convert the value 29 to type Table

Hello, 

im facing this error , 
Expression.Error: We cannot convert the value 29 to type Table.
Details:
Value=29
Type=[Type]

the code is as follows:

let
    Source = #"Master",
    NormLoading = #"Norm Loading",

    // Function to dynamically create columns in Yard Loading
    CreateNormLoadingColumn = (durationMonth as number) =>
        let
            // Filter Norm Loading table based on the condition
            filteredNorm = Table.SelectRows(NormLoading, each [Month] = durationMonth),
            
            // Extract the value from the first row of the filtered Norm Loading table
            result =
                if Table.RowCount(filteredNorm) > 0 then
                    Record.FieldOrDefault(filteredNorm{0}, "Value", 0)
                else null
        in
            result,

    // Get the list of Duration (Month) values from Yard Loading
    durationList = Source[#"Duration (Month)"],

    // Create a list of records with dynamically generated columns
    customColumnsList = List.Transform(durationList, each Table.AddColumn(_, "Month" & Text.From(_) & " Norms Loading", CreateNormLoadingColumn(_))),
    // Create a list of records with dynamically generated columns
    customColumnsList1 = customColumnsList{0}
in
    customColumnsList1
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi butterscotch 

     

    Based on the error message and the code snippet you've provided, it seems that there is an issue with the type conversion in your Power Query M code. Try the following steps:

     

    Ensure that "durationList" is indeed a list of numbers. If "Source[#"Duration (Month)"]" is not returning a list, this could be the source of the error.

     

    Review the "CreateNormLoadingColumn" Function. The function is expected to return a single value, not a table. Make sure that the "filteredNorm" table has a column named "Value" and that you are correctly extracting a scalar value from it.

     

    The "List.Transform" function is being used to apply "CreateNormLoadingColumn" to each item in "durationList". However, the result of "List.Transform" should be a list, not a table. 

     

    If you intend to combine all the tables in "customColumnsList" into one table, you should use "Table.Combine(customColumnsList)" instead of accessing the first element with "customColumnsList{0}".

     

    Verify that "Source[#"Duration (Month)"]" is a list of numbers.

     

    Confirm that the "Value" column exists in the "NormLoading" table.

     

    Replace the last part of the code with the correct method to combine the tables if that's the intended outcome.

     

    // Create a list of tables with dynamically generated columns
customColumnsList = List.Transform(durationList, each Table.AddColumn(Source, "Month" & Text.From(_) & " Norms Loading", CreateNormLoadingColumn(_))),

// Combine all tables into one
finalTable = Table.Combine(customColumnsList)

     

    Replace the last part of your code with the above snippet and see if it resolves the issue.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.