Forum Discussion
Retrieving the biggest text length value for every column in table
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!
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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!
