Aligning split columns

[Anonymous]

Hello,

I have the following in a column:

a,b,c

c,b,d

d,a,c

I can split columns using the delimiter so it becomes:

a	b	c
c	b	d
d	a	c

and I would like to have it presented like this, aligned to its own value column:

a	b	c
	b	c	d
a		c	d

I am familar with using a delimiter, but there a way to align the results to its own columns?

Thank you!

[OwenAuger · Accepted solution]

Hi @Anonymous 

I would recommend you

1. Split the values into a list contained within the column (not expanded into separate columns)
2. Convert each list to a record with field names equal to the values themselves
3. Expand the record columns

Here is some sample M code to illustrate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStRJ0klWitWJVkoGslLArBSdRJBYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    SplitToList = Table.TransformColumns( #"Changed Type", {{"Text",  Splitter.SplitTextByDelimiter(","),type {text}}} ),
    ConvertToRecord = Table.TransformColumns(SplitToList, {{"Text", each Record.FromList(_,_)}}),
    #"Expanded Text" = Table.ExpandRecordColumn(ConvertToRecord, "Text", List.Distinct(List.Combine(SplitToList[Text])))
in
    #"Expanded Text"

Regards

Owen 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

OwenAuge and Ashish_Mathur has shown you the way in PowerQuery Editor, and you can try my way by dax.

I use calculated columns to achieve your goal, after I split the value.

And I add an index column to sort the new columns.

A = IF('Table'[Column1.1]="a" ||'Table'[Column1.2]="a"||'Table'[Column1.3]="a","a",BLANK())

B = IF('Table'[Column1.1]="b" ||'Table'[Column1.2]="b"||'Table'[Column1.3]="b","b",BLANK())

C = IF('Table'[Column1.1]="c" ||'Table'[Column1.2]="c"||'Table'[Column1.3]="c","c",BLANK())

D = IF('Table'[Column1.1]="d" ||'Table'[Column1.2]="d"||'Table'[Column1.3]="d","d",BLANK())

Result:

You can download the pbix file from this link: Aligning split columns

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[OwenAuger · Accepted solution]

Hi @Anonymous 

I would recommend you

1. Split the values into a list contained within the column (not expanded into separate columns)
2. Convert each list to a record with field names equal to the values themselves
3. Expand the record columns

Here is some sample M code to illustrate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStRJ0klWitWJVkoGslLArBSdRJBYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    SplitToList = Table.TransformColumns( #"Changed Type", {{"Text",  Splitter.SplitTextByDelimiter(","),type {text}}} ),
    ConvertToRecord = Table.TransformColumns(SplitToList, {{"Text", each Record.FromList(_,_)}}),
    #"Expanded Text" = Table.ExpandRecordColumn(ConvertToRecord, "Text", List.Distinct(List.Combine(SplitToList[Text])))
in
    #"Expanded Text"

Regards

Owen 

[Paxro]

An elegant and effective process, thank you very much! This worked for a need similar to OP's post.