Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
i have table like here:
1 | DeploymentsMap |
6 | DeploymentsMap |
3 | DeploymentsMap |
6 | DeploymentsMap |
and what i want to get is:
TableName|Index
DeploymentsMap | 1;3;6 |
how to do this?
I will have multiple TableNAmes in a column...
Please help,
Jacek
Solved! Go to Solution.
Hi @jaryszek ,
Please try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJLcjJr8xNzSsp9k0sUIrViVYywy5sjF3Y0NrI2tjaxNoUu7QRUNjRyRnMNoGxYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter"),
#"Grouped Rows" = Table.Group( #"Removed Duplicates", {"Column2"}, {{"Combined", each Text.Combine(List.Sort(List.Distinct([Column1])), ";")}})
in
#"Grouped Rows"
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jaryszek ,
Please try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJLcjJr8xNzSsp9k0sUIrViVYywy5sjF3Y0NrI2tjaxNoUu7QRUNjRyRnMNoGxYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter"),
#"Grouped Rows" = Table.Group( #"Removed Duplicates", {"Column2"}, {{"Combined", each Text.Combine(List.Sort(List.Distinct([Column1])), ";")}})
in
#"Grouped Rows"
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can do that by creating a Group By step on the Home tab, and then modifying the code in the Formula Bar to match this example. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJLcjJr8xNzSsp9k0sUIrViVYywy5sTLzqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Combined", each Text.Combine(List.Sort(List.Distinct([Column1])), ";")}})
in
#"Grouped Rows"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you it is working!
One more thing.
Sometimes i will have already created strings as Value like "1;2;3;4;5":
1 | DeploymentsMap |
6 | DeploymentsMap |
3 | DeploymentsMap |
1;2;3;4;5 | DeploymentsMap |
And as result i would like to get
1;2;3;4;5;6 | DeploymentsMap |
so check if in list value exists, if not append. So check if there is alreadylist, if yes to not have duplicates -->
appendto get only uniqes.
what i get using provided formula:
Column2Combined
DeploymentsMap | 1;1;2;3;4;5;3;6 |
Can you please advise also?
Best,
Jacek
And the problem which i can have also that sometimes
Value can be an array like "1;2;3;4;5" and in this case i should keep whole array plus append missing rows.
So in this case i would append "6" to get "1;2;3;4;5;6"
Best,
Jacek
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |