Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Solved! Go to 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
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.
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
13 | |
12 | |
12 |