Forum Discussion
Data Profile: Min and Max Text Length
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.
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(_)))} })
2 Replies
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(_)))} })Thank you for taking the time to post the solution and an explanation. It worked wonderfully. Much appreciated!
