Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Gecald
Frequent Visitor

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

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.

 img.jpg

 Thanks in advance.

1 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

vxinruzhumsft_0-1716352143779.png

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.

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

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

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?

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

vxinruzhumsft_0-1716352143779.png

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.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.