I have a table, which I can't refresh due to the error "MashupException.Error: DataSource.Error: Microsoft SQL: String or binary data would be truncated."
My problem is that I have trouble debugging the issue and applying Text.Start to the affected columns doesn't work probably? The different ways of gauging the maximum string length in these columns give different results, 120 or 126 depending on how I do it.
An example script is below, and the returned length for each block is added as a comment. The Column's width in the destination table is 120, and the dataflow refresh fails with the above error, so 1 and 4 are likely correct, but I don't understand how these differing results are possible at all.
TabTextCut = Table.TransformColumnTypes(Table.TransformColumns(TabInhalt, {{"t_name", each Text.Start(_, 120)}}), {{"t_name", type text}})
,TargetTable = Table.AddColumn(//temp-table, used by methods 1-3
TabTextCut, "text_length", each [t_name] //Add column "text_length" as copy of "t_name
)
//126
,LongestString = List.Max(
List.Transform(
Table.ToList(
Table.SelectColumns(
TargetTable, "text_length"
)
), Text.Length
)
)
//120
,LongestStringAlt = List.Max(
List.Transform(
Table.ToList(
Table.SelectColumns(
Table.TransformColumns(
TargetTable, {{"text_length", (tex as nullable text) as nullable text => Text.From(Text.Length(tex)}}
), "text_length"
)
), Number.FromText
)
)
//120
,LongestStringTable = Table.First(
Table.Sort(
Table.TransformColumns(
TargetTable, {{"text_length", Text.Length}}
), {"text_length", Order.Descending}
)
)[text_length]
//126
,LongestStringDirect = List.Max(
List.Transform(
Table.ToList(
Table.SelectColumns(TabTextCut, "t_name") //Explicitely not TargetTable
)
, Text.Length
)Edit:
I have, as a test, cut off every string after the first letter with Text.Start(_, 1). The debug function below still manages to return maximum string lengths of 4 somehow. base_table is the table I am examining, column_length_map stores each column name of base_table, their data type and, for text types, their allowed length.
(base_table as table, column_length_map as table) as table =>
let
StringCols = Table.SelectRows(column_length_map, each [data_type] = "char" or [data_type] = "varchar"),
test2 = Table.AddColumn(Table.SelectColumns(StringCols, "staging_column"), "length", each
List.Max(
List.Transform(
Table.ToList(
Table.SelectColumns(base_table, Record.Field(_, "staging_column"))
)
, (val) => Text.Length(val)
)
)
)
, test3 = Table.Join(test2, "staging_column", Table.SelectColumns(column_length_map, {"staging_column", "max_length"}), "staging_column", JoinKind.Inner)
in
test3
