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.
Hello!
Is it a good idea to merge columns to achieve a faster join?
The idea would be to merge the columns with a specific delimiter, and keep only a large column and the ID column.
And then just split the big column by the delimiter chosen to have each column.
What do you think ? have you ever tested this?
Exemple
Thanks!
Solved! Go to Solution.
Hi,
Is it necessary the merge to get an unique value as result of the merge?
If not I don't get the point of merging the columns
Thanks
Hi @cher90 ,
According to your description, here's my solution.
1.Select the three columns at the same time(Ctrl+click), then click Transform>Merge Columns.
In the Separator option, you can select "Custom" and enter the symbol.
Result:
2. If you want to split the merged column, click Transform>Split Column>By Delimiter.
Enter the custom delimiter in the dialog.
Result:
Here's the whole M syntax, you can copy-paste in a blank query to see the details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEAiCVBARAKhkIlGJ1opWMyJIywSEVCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t, Col3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Col1", "Col2", "Col3"},Combiner.CombineTextByDelimiter("--", QuoteStyle.None),"Colx"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Colx", Splitter.SplitTextByDelimiter("--", QuoteStyle.Csv), {"Colx.1", "Colx.2", "Colx.3"})
in
#"Split Column by Delimiter"
I also attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Is it necessary the merge to get an unique value as result of the merge?
If not I don't get the point of merging the columns
Thanks
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.