Retrieving the biggest text length value for every column in table

[Snaske]

Hi!

I'm cleaning and preparing data before an erp migration, and I would prefer to have the task more automated since the it will be recurring.

I need to check every column for it's longest value (longest as in largest amount of characters) to ensure that the set length limitations in the target erp do not cut any values

Let's say I have data that looks like this:

Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Phone = "123-456712"],
        [CustomerID = 22, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 333, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4444, Name = "Ringo", Phone = "232-155042424"]
   })

Here I would want a table with two colums, or any viable solution, really. One with the column names from the source data, and one with a number representing the longest value for that column. Also preferably not hard coded with column names or similar, to be able to swiftly reuse for new cases.

Something like this:

ColName - Len

CustomerID - 4

Name - 5

Phone - 13

Hope you can crack this one!

[smpa01 · Accepted solution]

@Snaske 

Sure

[Snaske]

Thank you @Anonymous and @smpa01 for two good solutions, I really appreciate it.

I found a third, even better match for me in 
https://community.fabric.microsoft.com/t5/Power-Query/Data-Profile-Min-and-Max-Text-Length/td-p/3315028
since I already use the table profile for checking the data.

Until next time!

[Anonymous · Accepted solution]

Hi @Snaske ,

I suggest you to duplicate the above table and then do some transformation on it.

let
    Source = Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Phone = "123-456712"],
        [CustomerID = 22, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 333, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4444, Name = "Ringo", Phone = "232-155042424"]
   }),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Len", each Text.Length([Value])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Attribute"}, {{"Max Len", each List.Max([Len]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Attribute", "ColName"}})
in
    #"Renamed Columns"

Result is as below.

Best Regards,
Rico Zhou

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

[smpa01 · Accepted solution]

@Snaske 

Sure