Is it a good idea to merge columns to achieve a faster join?

[cher90]

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!

[v-yanjiang-msft]

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.

[AKNiang · Accepted 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