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

[Gecald]

Hi all,

I have this table that has repeated values in first column. For each repeated value in column1, I don't want to have differences in the rest of columns. Then for each repeated value in column1 I want to keep the value with longest length.

Don't how to do this kind of comparisons.

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}})
in
    #"Changed Type"

Below I show the input table to the left and to the right is the expected output. As you can see, for each repeated value in column1, I copied the longest string in column 3 and 4. For example, there are 3 repeated "ap3" in column1 and for each row of "ap3" the values in column3 are "226:::5731", "226" and "474:::226:::5731". The largest of those 3 is "474:::226:::5731", then I want to copy that value in the other rows of column3 related with "ap3", in this sample the same applies to value "ppd". The same process for column4. I hope make sense.

 

 Thanks in advance.

[AlienSx]

let
    Source = your_table,
    names = List.Buffer(Table.ColumnNames(Source)),
    longest = 
        {{"lst", (x) => Table.Column(x, List.First(names))}} & 
        List.Transform(
            List.Skip(names),
            (w) => 
                {w, (z) => 
                    List.Max(
                        Table.Column(z, w), null, 
                        (x, y) => Value.Compare(Text.Length(x), Text.Length(y))
                    )
                }
    ),
    group = Table.Group(Source, List.First(names), longest),
    xpand = Table.SelectColumns(Table.ExpandListColumn(group, "lst"), names)
in
    xpand

[Gecald]

Thanks so much for your help. It works perfect, the only thing is how would be to do it only for column 3 and 4? Additionally, is there is a way to export the table from power bi to CSV?

[v-xinruzhu-msft · Accepted solution]

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 ...

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.

[Gecald]

@v-xinruzhu-msft simply amazing. The solution, information shared and your kindness! Thank you