Table.Column with value (-4.80, 0.70) throws error when using as List

Question

Based on this answer https://stackoverflow.com/a/68625162/16578424 I am able to clean up a lot of data.

But now I have to fix a column that contains a cell with the value of (-4.80, 0.70) which returns an error when calling the column-function within my function

To me the error message sounds like if (-4.80, 0.70) is read as a list with two columns - and that throws the error :-/

This is the function. I commented everything out - and added the first two rows to demonstrate the problem.

//fnFixMultipleValuesInCell
//https://stackoverflow.com/a/68625162/1657842
let
    fnFixMultipleValuesInCell = (Source as table, columnName as text, columnIndex as number) as table =>
    let

        listColumn = Table.Column(Source, columnName), //same result for  listColumn = Source[Column2]
        Result = Table.FromList(listColumn)   /*,
        

        addIndex = Table.AddIndexColumn(Source, "Index",0),
        replaceNull = Table.ReplaceValue(addIndex,null,"",Replacer.ReplaceValue,{columnName}),

        splitMergedCells = List.Select(List.Combine(List.Transform( Table.Column(replaceNull, columnName), each Text.Split(_, "#(lf)"))), each Text.Length(_) > 0 ),
        
        insertColumn = Table.AddColumn(replaceNull, "new", each splitMergedCells{[Index]}) ,

        removeOldColumn = Table.RemoveColumns(insertColumn,{columnName, "Index"}),
        renameColumn = Table.RenameColumns(removeOldColumn,{{"new",columnName}}),
        ColumnNames = Table.ColumnNames(renameColumn),
        newColumnNamesList = List.RemoveLastN( List.Combine({List.FirstN(ColumnNames, columnIndex-2), List.LastN(ColumnNames,1), List.LastN(ColumnNames, List.Count(ColumnNames)-columnIndex+1 ) } ),1),
        Result = Table.ReorderColumns(renameColumn, newColumnNamesList) */

    in
        Result
in fnFixMultipleValuesInCell

Anything wrong with my code? Anything I can do, to use this function for any kind of column - regardless of the data?

Answer 1

You are correct that it is interpreting the comma as denoting an extra column.

If you look at the documentation for Table.FromList, you'll see the following:

Table.FromList(list as list, optional splitter as nullable function, optional columns as any, optional default as any, optional extraValues as nullable number) as table

Converts a list, list into a table by applying the optional splitting function, splitter, to each item in the list. By default, the list is assumed to be a list of text values that is split by commas. Optional columns may be the number of columns, a list of columns or a TableType. Optional default and extraValues may also be specified.

To fix this, change Table.FromList(listColumn) to

Table.FromList(listColumn, Splitter.SplitByNothing())