Keep the longest string in one column for related values in another column

Hi,

Thanks for the solution AlienSx   offered and i want to offer some more information for user to refer to.

hello Gecald , you can create a blank query and input the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/NCgIhFIVfRdyOBf6MV93NO7QIpllMaZugZJSBevquI8EUtWgjB/y+47Hv6RglZRSsxXP0fgopEY5ZCO2ca0FyOjDEzpeCSb7CxOHazVuyvz8YSXkKIZPueEKg0bLIANJZKxY/Ro8Xiq99zWrBLt9SxPxeYlpV1a8Lsf6VbV37my0Pg0Lj40//b4KlpjFGtBuB64Yn", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HEADER1 = _t, HEADER2 = _t, HEADER3 = _t, HEADER4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HEADER1", type text}, {"HEADER2", type text}, {"HEADER3", type text}, {"HEADER4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Length_header3", each Text.Length([HEADER3])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Length_header4", each Text.Length([HEADER4])),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [HEADER3],each let a=List.Max(Table.SelectRows(#"Added Custom1",(x)=>x[HEADER1]=[HEADER1])[Length_header3])
  in Table.SelectRows(#"Added Custom1",(x)=>x[HEADER1]=[HEADER1] and x[Length_header3]=a)[HEADER3]{0},Replacer.ReplaceValue,{"HEADER3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [HEADER4], each let a=List.Max(Table.SelectRows(#"Added Custom1",(x)=>x[HEADER1]=[HEADER1])[Length_header4])
  in Table.SelectRows(#"Added Custom1",(x)=>x[HEADER1]=[HEADER1] and x[Length_header4]=a)[HEADER4]{0},Replacer.ReplaceValue,{"HEADER4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Length_header3", "Length_header4"})
in
    #"Removed Columns"

Output

And the question about rexport data to csv, you can refer to the following link.

Export data from a Power BI visualization - Power BI | Microsoft Learn

Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods - BI Insight

Best Regards!

Yolo Zhu

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