Data Profile: Min and Max Text Length

[RoydenC]

Hello I am currently profiling my data using the Table.Profile() function and it's working. My issue is that I also need it to provide me the min and max length of the text in the data.

This works as intended.

= Table.Profile(My_Table, {
        {"Median", each Type.Is(_, type nullable number), List.Median},
        {"Mode_Number", each Type.Is(_, type nullable number), List.Mode},
        {"Mode_Text", each Type.Is(_, type nullable text), List.Mode},
        {"Mode_Date", each Type.Is(_, type nullable date), List.Mode},
    }
)

What I'm trying to do in the next piece of code is get the text length of each record and get the min text length. I'm a bit weak with lists and the below code doesn't work.

= Table.Profile(My_table, {
{"Median", each Type.Is(_, type nullable number), List.Median},
{"Mode_Number", each Type.Is(_, type nullable number), List.Mode},
{"Mode_Text", each Type.Is(_, type nullable text), List.Mode},
{"Mode_Date", each Type.Is(_, type nullable date), List.Mode},
{"Min Len" , each Type.Is(_, type nullable text), List.Min(Text.Length(each ))}
}
)

The alternative is me duplicating the data set, creating a new colum for each indifidual column in a table and getting the length, removing the original columns, renaming the length columns with the original column names and then merging the columns with the master data profile. This is a rather tedious task when you have multiple tables and multiple columns within a table. I am hoping there is a better way.

The data profile viewer shows the min/max text length of the data so not sure why the Table.Profile function doesn't or if I'm misisng something with the Table.Profile function.

[ronrsnfld · Accepted solution]

You need to create a List of the lengths in order to apply the LIst.Min or List.Max functions

profile = Table.Profile(#"Table to Profile", {
        {"Max Text Length", each Type.Is(_, type nullable text), each List.Max(List.Transform(_, each Text.Length(_)))},
        {"Min Text Length", each Type.Is(_, type nullable text), each List.Min(List.Transform(_, each Text.Length(_)))}
        })

[RoydenC]

Thank you for taking the time to post the solution and an explanation. It worked wonderfully. Much appreciated!