The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
24 | |
23 | |
21 | |
16 |